Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-04-2023, 04:47 PM
scienceguy scienceguy is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2019
Advanced Beginner
Unable to add tab stops to Word document via Excel VBA
 
Join Date: Feb 2019
Posts: 46
scienceguy is on a distinguished road
Default 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

Attached Images
File Type: png footer-right.png (2.5 KB, 9 views)
Reply With Quote
  #2  
Old 01-09-2023, 08:51 PM
Guessed's Avatar
Guessed Guessed is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,380
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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
Reply With Quote
  #3  
Old 01-10-2023, 11:11 AM
scienceguy scienceguy is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2019
Advanced Beginner
Unable to add tab stops to Word document via Excel VBA
 
Join Date: Feb 2019
Posts: 46
scienceguy is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 01-10-2023, 03:42 PM
Guessed's Avatar
Guessed Guessed is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,380
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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
Reply With Quote
  #5  
Old 01-11-2023, 07:24 AM
scienceguy scienceguy is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2019
Advanced Beginner
Unable to add tab stops to Word document via Excel VBA
 
Join Date: Feb 2019
Posts: 46
scienceguy is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 01-11-2023, 02:46 PM
Guessed's Avatar
Guessed Guessed is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,380
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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
Reply With Quote
  #7  
Old 01-11-2023, 02:53 PM
scienceguy scienceguy is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2019
Advanced Beginner
Unable to add tab stops to Word document via Excel VBA
 
Join Date: Feb 2019
Posts: 46
scienceguy is on a distinguished road
Default

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

Best wishes,
Roy
Reply With Quote
  #8  
Old 01-12-2023, 12:53 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Unable to add tab stops to Word document via Excel VBA Windows 10 Unable to add tab stops to Word document via Excel VBA Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,667
Pecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud of
Default

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
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft