|
|
#1
01-04-2023, 04:47 PM
|
|||
|
|||
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
|
|
#2
01-09-2023, 08:51 PM
|
||||
|
||||
|
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia
|
|
#3
01-10-2023, 11:11 AM
|
|||
|
|||
|
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
|
|
#4
01-10-2023, 03:42 PM
|
||||
|
||||
|
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia
|
|
#5
01-11-2023, 07:24 AM
|
|||
|
|||
|
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?
|
|
#6
01-11-2023, 02:46 PM
|
||||
|
||||
|
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia
|
|
#7
01-11-2023, 02:53 PM
|
|||
|
|||
|
Thank you again, Andrew! Very kind of you to help me!
Best wishes, Roy
|
|
#8
01-12-2023, 12:53 AM
|
||||
|
||||
|
Please mark thread as solved. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
|
|
| Tags |
| excel 365, word 365 |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Tabs in word like Excel | kdawgtx | Word | 1 | 01-16-2021 08:09 PM |
| Word Document Restricted to using Tabs to move to next place for input | swalsh96 | Word | 2 | 09-20-2016 04:41 AM |
| HELP: Unable to save word document | SteveJ | Word | 0 | 06-28-2013 04:06 AM |
| Unable to select anything in an MS Word Document | PaulT | Word | 0 | 08-08-2011 07:45 PM |
| Creating custom tabs for Word & Excel 2003 on a Windows 7 OS | Skarab | Office | 1 | 07-05-2010 09:23 AM |
Unable to add tab stops to Word document via Excel VBA
Windows 10 
Office 2019
Linear Mode
