Excel VBA: Inserting "Page X of Y" in a word document at the current cursor position

[MortenTN]

Hi MrExcel wizards.

I have been searching for hours trying to find something to solve this specific problem. I have information in an Excel file, which I want to transfer to a word file. I have everything working perfectly. All i now need is to add "Page X of Y" at a specific location.

I have found this code, which works perfectly in word itself, but I cannot get it to work Excel:

Sub pageNumber()
    
ActiveDocument.Sections(ActiveDocument.Sections.Count) .Headers(wdHeaderFooterPrimary).Range.Select
    With Selection
        
.Paragraphs(1).Alignment = wdAlignParagraphCenter
        .TypeText Text:="Page "
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= "PAGE ", PreserveFormatting:=True
        .TypeText Text:=" of "
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= "NUMPAGES ", PreserveFormatting:=True
    End With
End Sub

I am aware that I need to tweek the code to make Excel understand what to do. So i have tried to do that:

Sub main()
    Dim objWord As Word.Application
    Dim objDoc As Word.Document


    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    Set objDoc = objWord.Documents.Add()
    objDoc.Activate


    objWord.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader


    With objWord.Selection
        .TypeText Text:="Page "
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:="PAGE ", PreserveFormatting:=True
        .TypeText Text:=" of "
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:="NUMPAGES ", PreserveFormatting:=True
    End With
End Sub

I just want the code to insert "Page X of Y" at the current position of the cursor. I hope someone can help with this.

Best regards,
Morten

 

[Macropod]

Page X of Y constructs usually go into a Section page header or footer, not merely at 'the current position of the cursor' and, when inserted, apply to the entire Section, not just the current page. Moreover, it's normal to base any new document on a template that has all the 'boilerplate' content of this kind already in place, thus obviating the need to add that content via code. Is there a reason you can't use such a template?

 

[MortenTN]

Hi Macropod,

I do not mean that I want to only do it in the section the cursor is currently in. I mean that the "Page X of Y" should be inserted at the current position of my cursor.

The Word VBA code described in my first post does just that - Insert the "Page X of Y", at the current position of the cursor. I therefore do not understand why Excel are not able to replicate it. I guess I could setup a template specifically for this problem, but it would be nice to know how I can solve this problem "correctly" with code.

 

[Macropod]

It is quite simple to do in code but, as I said, the better way is to use a template. That has numerous advantages, including easier maintenance. In any event, what your code does really has nothing to do with 'the current position of the cursor', since you're creating a new document. Code:

Sub Main()
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
  Set wdDoc = .Documents.Add()
  With wdDoc
    With .Sections(1).Headers(wdHeaderFooterPrimary).Range
        .InsertAfter Text:="Page "
        .Fields.Add Range:=.Characters.Last, Type:=wdFieldEmpty, Text:="PAGE", PreserveFormatting:=False
        .InsertAfter Text:=" of "
        .Fields.Add Range:=.Characters.Last, Type:=wdFieldEmpty, Text:="NUMPAGES", PreserveFormatting:=False
    End With
  End With
  .Visible = True
End With
End Sub

If you used a template (as you should), all you'd need is:

Sub Main()
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
  Set wdDoc = .Documents.Add(Template:="Template path & name")
  .Visible = True
End With
End Sub

 

[MortenTN]

Thanks for your help macropod - that solved my problem.