VBA - footer

[j4ymf]

Hello

We use this VBA code in our footer where it exports to a word document, but is there a way i can add a page number aswell?

    ' Add text to footer in left-aligned
    footerText = " Company info "

    ' Add footer to the document
    With wddoc.Sections(1).Footers(1).Range ' wdHeaderFooterPrimary = 1
        .Text = footerText
        .ParagraphFormat.Alignment = wdAlignParagraphLeft ' Left-align the footer
        .Font.Name = "Century Gothic"
        .Font.Size = 10
        .Font.Color = RGB(56, 56, 56) ' Dark grey/black color
        ' Removed the left indent for no spacing
        .ParagraphFormat.SpaceAfter = 1 ' No space after the footer
        .ParagraphFormat.SpaceBefore = 0 ' No space before the footer
    End With

 

[Trevor G]

You can record a macro in Word to add the page number then you'll have the code you can add to your present code.

 

[Tetra201]

Try adding the following code after End With:

    Set frng = wddoc.Sections(1).Footers(1).Range
    frng.InsertAfter vbTab & vbTab & "Page "
    frng.Collapse Direction:=wdCollapseEnd
    frng.Fields.Add Range:=frng, Type:=wdFieldPage, PreserveFormatting:=True
    Set frng = wddoc.Sections(1).Footers(1).Range
    frng.InsertAfter " of "
    frng.Collapse Direction:=wdCollapseEnd
    frng.Fields.Add Range:=frng, Type:=wdFieldNumPages, PreserveFormatting:=True

 

[j4ymf]

Try adding the following code after End With:

    Set frng = wddoc.Sections(1).Footers(1).Range
    frng.InsertAfter vbTab & vbTab & "Page "
    frng.Collapse Direction:=wdCollapseEnd
    frng.Fields.Add Range:=frng, Type:=wdFieldPage, PreserveFormatting:=True
    Set frng = wddoc.Sections(1).Footers(1).Range
    frng.InsertAfter " of "
    frng.Collapse Direction:=wdCollapseEnd
    frng.Fields.Add Range:=frng, Type:=wdFieldNumPages, PreserveFormatting:=True

Hello Tetra201

Thank you for coming back to me over the festive period.
the macro runs and fails, it gets as far as adding in the word "Page" to the footer but fails on this line.

any ideas

 

[Tetra201]

Thank you for the feedback.

Where from are you running the code? -- Excel? Word? Something else?
Are there any error messages when the macro fails?

 

[Macropod]

For example:

Const footerText As String = " Company info "
Const wdHeaderFooterPrimary As Long = 1: Const wdFieldEmpty As Long = -1
Const wdCollapseStart As Long = 1: Const wdAlignParagraphLeft As Long = 0
Const wdAlignTabRight As Long = 2: Const wdTabLeaderSpaces As Long = 0
With wddoc.Sections(1).Footers(wdHeaderFooterPrimary).Range
    .Font.Name = "Century Gothic"
    .Font.Size = 10
    .Fields.Add .Duplicate, wdFieldEmpty, "NUMPAGES", False
    .Collapse wdCollapseStart
    .Text = " of "
    .Collapse wdCollapseStart
    .Fields.Add .Duplicate, wdFieldEmpty, "PAGE", False
    .Collapse wdCollapseStart
    .Text = vbTab & "Page "
    .ParagraphFormat.Alignment = wdAlignParagraphLeft
    .ParagraphFormat.TabStops.Add Position:=InchesToPoints(6), Alignment:=wdAlignTabRight, Leader:=wdTabLeaderSpaces
    .ParagraphFormat.SpaceAfter = 0
    .ParagraphFormat.SpaceBefore = 0
    .Collapse wdCollapseStart
    .Text = footerText
    .Font.Color = RGB(56, 56, 56) ' Dark grey/black color
End With

 

[j4ymf]

Hi Macropod

thanks for the reply
I get this Compile error:

any ideas?

 

[j4ymf]

Hi Tetra201
I get a run time error

thank for your help

 

[Macropod]

Try changing:
Position:=InchesToPoints(6)
to:
Position:=wdapp.InchesToPoints(6)
where 'wdapp' is whatever you're calling the Word application.

 

[Tetra201]

For my suggestion (Post #3), try changing all named constants to their values like this:

    Set frng = wddoc.Sections(1).Footers(1).Range
    frng.InsertAfter vbTab & vbTab & vbTab & vbTab & "Page "
    frng.Collapse Direction:=0
    frng.Fields.Add Range:=frng, Type:=-1, Text:="PAGE  ", PreserveFormatting:=True
    Set frng = wddoc.Sections(1).Footers(1).Range
    frng.InsertAfter " of "
    frng.Collapse Direction:=0
    frng.Fields.Add Range:=frng, Type:=-1, Text:="NUMPAGES  ", PreserveFormatting:=True