From Excel create a footer in Word

[JKwan]

Wondering if there are Word expert here...., first time working with Word VBA and is not doing well....

I would like to create a multi column footer in my Word document from Excel:
Left column I just wanted a text of "Confidential"
Middle column I wanted a text of "blah" - don't know what to call it yet
Right column I would like "Page x of y"

Been at it all morning, just not able to do it!

Thanks.

[snb]

Use the macrorecorder in Word.
Use a 3 columns-table in the footer of Word.

[JKwan]

Thanks for the suggestion, snb, I did try to do that but it is not working the way I thought it would.... Mainly the text are being inserted into the body, rather into the Footer. This is what I recorded. I need to somehow get to the Footer area, then run the footer bit, during the RECORDING of the macro, I cannot access the FOOTER!!??


Sub Macro1()
'
' Macro1 Macro
'
'
Application.Templates( _
"C:\Users\JKWAN\AppData\Roaming\Microsoft\Document Building Blocks\1033\14\Built-In Building Blocks.dotx" _
).BuildingBlockEntries(" Blank").Insert Where:=Selection.Range, RichText _
:=True
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _
3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Grid" Then
.Style = "Table Grid"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
.ApplyStyleRowBands = True
.ApplyStyleColumnBands = False
End With
Selection.TypeText Text:="Confidential"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="blah"
Selection.MoveRight Unit:=wdCell
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"PAGE ", PreserveFormatting:=True
Selection.TypeText Text:=" of "
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"NUMPAGES ", PreserveFormatting:=True
End Sub

Another thing is that the TEMPLATE bit, since not everyone may or may not have the same templates. I would like to eliminate that if I can

[snb]

Save the attachment somewhere.

Use this macro in Excel (adapt the path first)


Sub M_snb()
With GetObject("G:\OF\_footersnb.docx")
With .storyranges(9).tables(1)
.cell(1, 1).Range.Text = "Not Very_Confidential"
.cell(1, 2).Range.Text = " No Secret"
.cell(1, 3).Range.Text = "Extremely Classified"
End With
.Close -1
End With
End Sub

[JKwan]

snb, thank you for your example. I did more digging I finally found a solution :-)
Here is the code


With myDoc
oWord.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
For Each oSec In .Sections
' For Each oHeader In oSec.Headers
' If oHeader.Exists Then
' Set oRng = oHeader.Range
' With oRng
' .Font.Name = "Arial"
' .Font.Size = "20"
' .Font.Color = wdColorDarkBlue
' .ParagraphFormat.Alignment = wdAlignParagraphRight
' .Text = strPath
' End With
' End If
' Next oHeader
For Each oFooter In oSec.Footers
If oFooter.Exists Then
Set oRng = oFooter.Range
With oRng
With .ParagraphFormat.TabStops
.ClearAll
.Add CentimetersToPoints(7.96), wdAlignTabCenter
.Add CentimetersToPoints(15.92), wdAlignTabRight
End With
.Font.Name = "Arial"
.Font.Size = "10"
.Font.Color = wdColorDarkBlue
.Text = "Confidential" & vbTab & "Blah" & vbTab & "Page "
.Collapse wdCollapseEnd
.Fields.Add oRng, wdFieldPage, , False
.Start = oFooter.Range.End
.Text = " of "
.Collapse wdCollapseEnd
.Fields.Add oRng, wdFieldNumPages, , False
.Start = oFooter.Range.End
.Text = vbCr & "Blah 1" & vbTab & "Blah 2" & vbTab & "Blah 3"
.Borders.Enable = True
End With
End If
Next oFooter
Next oSec
oWord.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
End With

[snb]

Well I'd say that's the unnecesary complicated approach and I wouldn't label it a 'solution'. You shouldn't use VBA to do any formattting; the concept of templates has been invented for that purpose.