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

Microsoft Office Forums (https://www.msofficeforums.com/)

- Excel Programming (https://www.msofficeforums.com/excel-programming/)

- -

Unable to add tab stops to Word document via Excel VBA (https://www.msofficeforums.com/excel-programming/50190-unable-add-tab-stops-word-document-via.html)

Unable to add tab stops to Word document via Excel VBA

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

I'm thinking you need to convert the word constants into their integer values
wdAlignTabCenter = 1
wdAlignTabRight = 2
wdTabLeaderSpaces = 0

Code:

'So try changing this line

rng.ParagraphFormat.TabStops.Add Position:=InchesToPoints(3.25), Alignment:=wdAlignTabCenter, Leader:=wdTabLeaderSpaces



'to

rng.ParagraphFormat.TabStops.Add Position:=InchesToPoints(3.25), Alignment:=1, Leader:=0

Thanks, Andrew, for the reply! I had a weird response. I ran the code as you suggested, and it worked. I closed my test file without saving and re-ran the code. This time, the code behaved as before and didn't create the tab stops. I didn't change anything. Not sure why it works sometimes and not others.

Thanks again,
Roy

If there is a reference to the Word Object Library then the wdConstants appear to work for me so my earlier advice should be ignored. I had a fiddle with your code to actually test it and adapt it for various page setups. It works for me on multiple files so I'm not sure why you would get variable results. Can you test this code on your machine?

Code:

Sub createFooter()

  'Needs a reference to Microsoft Word xx.x Object Library

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

  Dim k As Integer, strFile As String

  

  With Application.FileDialog(msoFileDialogOpen)

    .Filters.Clear

    .Filters.Add "Word Files", "*.doc*", 1

    If .Show = -1 Then strFile = .SelectedItems(1)

  End With

  If strFile = "" Then Exit Sub

        

  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).LinkToPrevious = False

    Set rng = wdDoc.Sections(k).Footers(wdHeaderFooterPrimary).Range

    With rng

      .Delete         'delete everything in current footer

      .ParagraphFormat.TabStops.ClearAll

      .Font.Name = "Times New Roman"

      .Font.Size = 12

      .Text = "Confidential Information"

      .Collapse wdCollapseStart

      .InsertAlignmentTab Alignment:=wdRight, RelativeTo:=wdMargin

      .Collapse wdCollapseStart

      .Fields.Add rng, wdFieldPage, , False

      .Collapse wdCollapseStart

      .InsertAlignmentTab Alignment:=wdCenter, RelativeTo:=wdMargin

      .Text = "Company"

    End With

  Next

  

  Set wdDoc = Nothing

  Set wdApp = Nothing

  MsgBox "Footer added or replaced"

End Sub

Many, thanks, Andrew! Truly appreciate your efforts! This worked. Out of curiosity, do you think it’s not possible to set a positional tab stop in Word from Excel VBA?

I can't see any reason why the positioned tab would not work with your code but it would be problematic because you don't know the page width of every section.

If the page setup varies across sections or documents then using a static 6.5 inches as your assumed width is not going to work with every section. The 'special' alignment tabs adapt to the page setup width of every section so you could even use 'same as previous' for all footers after the first section.

Thank you again, Andrew! Very kind of you to help me!

Best wishes,
Roy

Please mark thread as solved. Thx