Excel Vba to Word: How to write a pagenumber into a textframe?

Question

I am writing an Excel VBA macro that copies text to a Word for Windows file and  adds formatting to it later.

It uses a .dotx template that contains a logo. In the lower left corner is a textbox with a serial-number. The text of the serial-number is written  vertically (from the bottom upwards).

Using trial and error I managed to write one serial-number to the textbox  using:

serialnumber = "abc1x"
wdoc.Sections(1).Headers(wdHeaderFooterEvenPages).Shapes(2).TextFrame.TextRange.text 
= serialnumber

So I found the right object to write to. Now I get the same serialnumber on every page.

My goal is to get an increasing serialnumber on the pages: The serial-number has the shape:

• On page 1:       abc1x
• on page 2:        abc2x
• on page 3:        abc3x
• ...
• on page 10:     abc10x

It's the page number surrounded by 2 strings.

On a different project I did something similar. I wrote "Page 1 of 10" etc. with the following script:   

    Dim uRange As Object
    Dim uneven As Object

    Set uneven = wdoc.Sections(1).Footers(wdHeaderFooterPrimary)
    Set uRange = wdoc.Sections(1).Footers(wdHeaderFooterPrimary).Range
    uRange.Delete

    uneven.Range.InsertAfter "Page "
    uRange.SetRange Start:=uneven.Range.End + 1, End:=uneven.Range.End + 1
    wdoc.Sections(1).Footers(wdHeaderFooterPrimary).Range.Fields.Add 
Range:=uRange, Type:=wdFieldEmpty, text:= _
    "PAGE  \* Arabic ", PreserveFormatting:=True

    uRange.SetRange Start:=uneven.Range.End + 1, End:=uneven.Range.End + 1
    uneven.Range.InsertAfter " of "
    uRange.SetRange Start:=uneven.Range.End + 1, End:=uneven.Range.End + 1

    wdoc.Sections(1).Footers(wdHeaderFooterPrimary).Range.Fields.Add 
Range:=uRange, Type:=wdFieldEmpty, text:= _
     "NUMPAGES  \* Arabic ", PreserveFormatting:=True

How do I insert the text around the Page field in the TextBox?

(Side note: What is the difference between the range and rangetext object?)

Remarks: I will have to apply the solution to the even and uneven pages seperately. That doesn't pose a problem. To make matters more difficult: I am obliged to keep the text-field, because it comes from the hands of  the corporate identity folks.

Answer 1

There are a number of ways to go about this. All involve "collapsing" the target Range before inserting the next thing (text or field code).

Some time ago, I wrote a set of generalized functions so that I could insert any combination of text and field codes comfortably, without having to "tweak" for each combination.

First define the Range object. If there might be any content you want to keep, collapse it. The procedures InsertNewText and InsertNewField take the target Range and the text to insert, respectively the field code for the field to be inserted. The collapsing of the Range is done in these procedures and is passed back to the calling procedure for the next step.

Sub InsertTextAndFields()
    Dim rngContent As Word.Range

    Set rngContent = wdoc.Sections(1).Headers( _
        wdHeaderFooterEvenPages).Shapes(2).TextFrame.TextRange
    rngContent.Collapse wdCollapseEnd

    Set rngContent = InsertNewText(rngContent, "abc")
    Set rngContent = InsertAField(rngContent, "Page")
    Set rngContent = InsertNewText(rngContent, "x")

End Sub

Function InsertNewText(rng As word.Range, newText As String) As word.Range
    rng.Text = newText
    rng.Collapse wdCollapseEnd
    Set InsertNewText = rng
End Function

Function InsertAField(rng As word.Range, _
                      fieldText As String) As word.Range

    Dim fld As word.Field
    Dim rngField As word.Range

    Set fld = rng.Document.Fields.Add(Range:=rng, _
              Text:=fieldText, PreserveFormatting:=False)

    Set rngField = fld.result
    rngField.Collapse wdCollapseEnd
    rngField.MoveStart wdCharacter, 1
    Set InsertAField = rngField
End Function