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

MortenTN

New Member
Joined
Sep 12, 2018
Messages
3
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:
Code:
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:
Code:
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
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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?
 
Upvote 0
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.
 
Upvote 0
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:
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:
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top