Microsoft Office Forums - View Single Post - [Solved] Unable to add tab stops to Word document via Excel VBA

scienceguy · 01-04-2023, 04:47 PM

Hi All,

I have several utility applications that I run on Word documents using Excel VBA. I use Excel to track the documents. I wanted to write some code that would programmatically redo the footer in a Word document, with the company name at the left margin, the page number centered, and "confidential information" on the right margin. I originally wrote this in Word VBA, and everything worked fine with some tweaking from a friend. However, when I adjusted the code to run from Excel, I am unable to create tabs in the footer. When finished, the footer should look like the attached image.

Most of the code works as expected. I just can seem to set the tabs with Excel VBA. The code is below.

For sake of illustration, I am only working on the primary footer.

Thanks in advance for any assistance.

Roy

Code:

Sub createFooter()

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim rng As Word.Range

With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Word Files", "*.doc*", 1

    If .Show = -1 Then
        strFile = .SelectedItems(1)
    End If
End With
If strFile = "" Then
    Exit Sub
End If
    
      
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then

    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True

End If

Set wdDoc = wdApp.Documents.Open(fileName:=strFile, AddToRecentFiles:=False, Visible:=True)

For k = 1 To wdDoc.Sections.Count

    wdDoc.Sections(k).Footers(wdHeaderFooterPrimary).Range.Delete 'delete everything in current footer

    wdDoc.Sections(k).Footers(wdHeaderFooterPrimary).LinkToPrevious = False

    Set rng = wdDoc.Sections(k).Footers(wdHeaderFooterPrimary).Range
       
    rng.ParagraphFormat.TabStops.ClearAll
    
    rng.ParagraphFormat.TabStops.Add Position:=InchesToPoints(3.25), Alignment:=wdAlignTabCenter, Leader:=wdTabLeaderSpaces
    
    rng.ParagraphFormat.TabStops.Add Position:=InchesToPoints(6.5), Alignment:=wdAlignTabRight, Leader:=wdTabLeaderSpaces
    
    rng.Font.Name = "Times New Roman"
    
    rng.Font.Size = 12
        
    rng.Text = "Company" & vbTab
    
    rng.Collapse wdCollapseEnd
    
    rng.Fields.Add rng, wdFieldPage, , False
       
    wdDoc.Sections(k).Footers(wdHeaderFooterPrimary).Range.InsertAfter vbTab & "Confidential Information"
        
Next

Set wdDoc = Nothing
Set wdApp = Nothing


MsgBox "Footer added or replaced"


End Sub