Delete Page Break in word using VBA in excel

[excelpunk]

Hello All!

I have a word template which is of 2 pages, it has just one page break, I have vba code in Excel, which opens this template, manipulates something and then saves the template with a different name.

However, I have now realized that in some cases I need to delete that page break. What vba code should I include to make this happen.

My apologies if the post is not relevant.

 

[ZVI]

Hi,
Manual PageBreak in Word is actually CHR(12) symbol.
Find CHR(12) and replace it by empty string "" like this:

Sub DeletePageBreak()
  
  Const MyFile = "C:\Temp\Test.dot"  ' <-- Change to suit
  
  Const wdReplaceOne = 1, wdReplaceAll = 2
  
  Dim objWord As Object, objDoc As Object
      
  ' Get/Create Word Application object using late binding method
  On Error Resume Next
  Set objWord = GetObject(, "Word.Application")
  If Err <> 0 Then Set objWord = CreateObject("Word.Application"): Err.Clear
  
  ' Open MyFile
  Set objDoc = objWord.Documents.Open(MyFile)
  If Err <> 0 Then MsgBox "Can't open: " & MyFile, vbExclamation: GoTo exit_
  On Error GoTo 0
  
  ' Activate Microsoft Word window
  objWord.Visible = True
  objWord.Tasks("Microsoft Word").Activate
  
  ' ... Do something else ...
  
  ' Delete PageBreak
  With objWord.ActiveDocument.Content.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = Chr(12)
    .Replacement.Text = ""
    .Forward = True
    .Format = False
    .Execute Replace:=wdReplaceOne  ' <-- Use wdReplaceAll if required
  End With
  
exit_:
  ' Release the memory
  Set objDoc = Nothing
  Set objWord = Nothing

End Sub

 

[ZVI]

This looks more strictly: With objDoc.Content.Find
than: With objWord.ActiveDocument.Content.Find
But the result is the same.

 

[excelpunk]

Thank you! It worked like charm! It may be a very simple piece of code to you but it saved me a lot of time and energy. Thanks again!

 

[ZVI]

Thank you! It worked like charm! It may be a very simple piece of code to you but it saved me a lot of time and energy. Thanks again!

Glad it helped you!

 

[excelpunk]

Hi ZVI!

I guess I have to trouble you again!

Can you please help me out how to sort a table in word using vba from Excel?

I recorded a macro in word and then tried to use it from Excel, I dont know why but it keep throwing error!

This is how my code looks:

with wordtemp.activedocument.tables(1).Select
'followed by the sort macro which I got after recording...

I was just wondering what is going wrong! Controling word from excel is a difficult task isint it?

 

[ZVI]

Hi excelpunk,

See below the sorting section in Red:

Sub DeletePageBreak_SortTables1()
  
  Const MyFile = "C:\Temp\Test.dot"  ' <-- Change to suit
  
  Const wdReplaceOne = 1, wdReplaceAll = 2, wdSortOrderAscending = 0
  
  Dim objWord As Object, objDoc As Object
      
  ' Get/Create Word Application object using late binding method
  On Error Resume Next
  Set objWord = GetObject(, "Word.Application")
  If Err <> 0 Then Set objWord = CreateObject("Word.Application"): Err.Clear
  
  ' Open MyFile
  Set objDoc = objWord.Documents.Open(MyFile)
  If Err <> 0 Then MsgBox "Can't open: " & MyFile, vbExclamation: GoTo exit_
  On Error GoTo 0
  
  ' Activate Microsoft Word window
  objWord.Visible = True
  objWord.Tasks("Microsoft Word").Activate
  
  ' ... Do something else ...
  
  ' Delete PageBreak
   With objDoc.Content.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = Chr(12)
    .Replacement.Text = ""
    .Forward = True
    .Format = False
    .Execute Replace:=wdReplaceOne  ' <-- Use wdReplaceAll if required
  End With
  
  ' Sort Tables(1)
  objDoc.Tables(1).Range.Sort ExcludeHeader:=True, FieldNumber:=1, SortOrder:=wdSortOrderAscending
  
exit_:
  ' Release the memory
  Set objDoc = Nothing
  Set objWord = Nothing

End Sub

Regards

 

[excelpunk]

Hi! Thanks for the reply. I will test it now but a quick question. My table has 3 columns, so when I read your code it says fieldnumber =1. Does values in other columns get adjusted? I mean, does it sort only column1 or it sorts all the columns?

 

[excelpunk]

Thanks again! This worked the way I wanted. Just amazing! Thanks.

Just a couple of queries:

Can you tell me what went wrong with the code I tried with:
with wordtemp.activedocument.tables(1).Select
'followed by recorded macro
Selection.Sort ExcludeHeader:=True, FieldNumber:="Column 1", SortFieldType _
:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending, FieldNumber2 _
:="", SortFieldType2:=wdSortFieldAlphanumeric, SortOrder2:= _
wdSortOrderAscending, FieldNumber3:="", SortFieldType3:= _
wdSortFieldAlphanumeric, SortOrder3:=wdSortOrderAscending, Separator:= _
wdSortSeparateByCommas, SortColumn:=False, CaseSensitive:=False, _
LanguageID:=wdEnglishUS, SubFieldNumber:="Paragraphs", SubFieldNumber2:= _
"Paragraphs", SubFieldNumber3:="Paragraphs"

Also, do you have links to any resources where controling word from Excel is explained?
I dont understand why the recorded macro throws error when I use it from Excel! The same code works fine when I use it in word modules. Strange!

 

[ZVI]

Hi! Thanks for the reply. I will test it now but a quick question. My table has 3 columns, so when I read your code it says fieldnumber =1. Does values in other columns get adjusted? I mean, does it sort only column1 or it sorts all the columns?

In my example all rows of Tables(1) are included in the sorted Range to be sorted by the order of Columns(1) like for the Range of Excel.