Solved: Word document footers

[muttleee]

Hiyas :-)

I have vba code which exports data from Lotus Notes to Word and prints it in a nicely formatted table. My problem now is how to add a footer to the bottom of each page of the report showing page number/date etc. It sounds so simple but I'm stuck. The examples I've seen in the forum generally show a separate subroutine but I sorta thought it should be doable in a line or 2 when I am setting up the page. The 'page set-up' bit of my code is as follows:

Sub Initialize

Dim worddocument As Variant
Dim session As New NotesSession
Dim db As notesdatabase
Set db=session.currentdatabase
Dim Num As Integer
Dim x As Integer
Dim ws As New NotesUIWorkspace

'further preliminary waffle/preparations/calculations etc go here.

'set up table in Word document
Set wdApp = CreateObject("Word.Application")
Set worddocument = wdApp.Documents.Add()
wdApp.visible = True

With wdApp

.selection.pagesetup.Orientation = wdOrientLandscape
.selection.ParagraphFormat.Alignment=1
.selection.font.name="Arial"
.selection.font.size = 10
.selection.font.bold=True
.selection.typetext("'Report for all Target Owners")
.selection.TypeParagraph
.selection.ParagraphFormat.Alignment=0
.selection.font.underline=False
.selection.typetext("Date of printing: " & Today())
.selection.TypeParagraph
.selection.TypeParagraph

.selection.font.bold=False

'set up table dimensions
Set TableObj= .selection.Tables.Add(wdApp.Selection.Range, NoRiskTargCount+riskcollection.count+1, 9)

'use reduced table of 9 columns
TableObj.Columns(1).Width = 190
TableObj.Columns(2).Width = 50
'etc

'add column headings
TableObj.cell(1,1).select
.selection.font.bold=True
.selection.typetext("Target")
TableObj.cell(1,2).select
.selection.font.bold=True
.selection.typetext("Status")
'etc...............
End With

'further processing of docs and printing values in table

End Sub I had (foolishly, it seems) thought that I could use something like a property of wdApp.selection.pagesetup to achieve what I want, like I already do for page orientation. No joy. If anyone can point me in the right direction I would be very grateful. If you can give me the Janet-and-John-version instructions, so much the better. :-) Thanks in advance! :bow:

[TonyJollans]

Hi muttleee,

Footers belong to Document Sections and are accessible in VBA via Document.Sections(1).Footers(1).

It can be a bit fiddly adding Fields to them, so post back if you need more help.

[sandam]

You can try something like this. I've found it worked for me to put page numbers in


With ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range
.Select
Selection.Font.Size = 8
Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
Selection.TypeText Text:="Page "
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldPage, PreserveFormatting:=True
Selection.MoveRight Unit:=wdCharacter, count:=1
Selection.TypeText Text:=" of "
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldNumPages, PreserveFormatting:=True
End With

[fumei]

There are a couple of threads on this.

One way is to make a Range of the footer and use the .InsertAutoText method to insert the standard Page X of Y, or Filename Autotext entries.

[muttleee]

Hi again,

Thanks for your responses. I have now got something sorted for the date at least, using the ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary) thingy. Here's the code I used:

With worddocument.Sections(1).Footers(wdHeaderFooterPrimary)
.Range.Text = "Report printed on " & Today()
.Range.Font.Size = 10
.Range.Font.Name = "Arial"
.Range.ParagraphFormat.Alignment = wdAlignParagraphRight
End With

I don't know if this is considered good code or not but it works. :-p When I tried Sandam's code, it didn't seem to like anything from the line "Selection.TypeText Text:="Page "" down so I couldn't even save it. It seems to have a problem with the equals signs in those lines. I'll keep playing about with it though. I imagine I'm just missing something mind-bogglingly obvious.

Thanks again for your help, one and all. :)

[fumei]

Just a couple of comments.

"With worddocument.Sections(1).Footers(wdHeaderFooterPrimary) " will ONLY affect the Primary headerfooter object for Section 1 ONLY. So if, you have that section with DifferentFirstPage, or DifferentOdd/Even then the results may ne bo what you expect.

2. If you have any other sections beyond Sections(1) - this will nothing to them.

3. I would put the format of the range.text BEFORE the insertion of the text, at least i fyou want to make sure thw inserted text IS that format.

[muttleee]

Thanks for the tips. I'm just using the same simple footer across all pages of the report so I think this should do the job. I have now got to the following:
With worddocument.Sections(1).Footers(wdHeaderFooterPrimary)
.Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Range.Text = "Report printed on " & Today()

.PageNumbers.StartingNumber = 1
.PageNumbers.Add

With .Range
.Font.Size = 10
.Font.Name = "Arial"
End With

End With
which gives me both the date on the left and page number on the right. However the page number is just that - a number, with no "Page x of y" etc (which would be nice). I did try the font/size attributes before the ".Range.Text =..." line but if I did that, the page numbers still came out as Times 12. It was only by putting the formatting at the end that both the date and the numbers came out as Arial 10.

[fumei]

Uh...please read my post offering the idea of making a Range, then using InsertAutoText. Something like:
Dim oRange As Word.Range
Set oRange = _
ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range
With oRange
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Text = "Report printed on " & FormatDateTime(Now, vbShortDate) _
& vbTab
.Collapse direction:=wdCollapseEnd
.InsertAfter Text:="Page X of Y"
.InsertAutoText
.Fields.Update
.WholeStory
With .Font
.Size = 10
.Name = "arial"
End With
End With
Set oRange = Nothing

Note that I put a Tab after the date, and explicitly formatted the date. You used Today()....whatever.

[muttleee]

Hi fumei,

I did attempt to use the InsertAutoText function that you mentioned in your earlier post but I couldn't get it to work. I'm a total beginner at this, don't forget. http://vbaexpress.com/forum/images/smilies/001.gif What happened was that when I ran the code, the table would be created in the word document and then what seemed like a separate little footer window would open at the bottom of the page with the usual buttons for inserting date and page numbers etc. Execution stopped at this point but if I then clicked a button, it would create the rest of the document as normal. This was one of the various methods I tried but because it seemed to require some user input, I ended up going with the method mentioned above. This was the first one that actually gave me what I was looking for even if there was no formatting to speak of. Presumably if I had used your method properly, I wouldn't have had to click the button to get page numbers. I'll give it another try now and see if I can get any further with it. http://vbaexpress.com/forum/images/smilies/001.gif

[fumei]

It did not work because my code uses a Range object, as in:
Dim oRange As Word.Range
Set oRange = _
ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range

etc. etc.

This means an explicit object has be created of that range.

Your code does not. Your code uses a With statement on the Range. My code does not require, or ask, for any user input. Try:
Dim oRange As Word.Range
Set oRange = worddocument.Sections(1).Footers(wdHeaderFooterPrimary).Range

With oRange
.etc etc.

[muttleee]

Here's what I have now.
'Dim oRange As Word.Range
Set oRange = wordDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range
With oRange
.ParagraphFormat.Alignment = 1
.Text = "Report printed on " '& FormatDateTime(Now, vbShortDate) & vbTab
.Collapse direction = wdCollapseEnd

' .InsertAfter Text := "Page X of Y"
' .InsertAutoText
' .Fields.Update
' .WholeStory
With .Font
.Size = 10
.Name = "arial"
End With
End With
Set oRange = Nothing

If I uncomment any or all of the comented bits above I get compile errors. If I include the date, it says "Illegal parenthesized reference".

If I uncomment the .InsertAfter Text line. it says "Unexpected:=; Expected: Statement" It doesn't like the dot in the Dim statement either.

Still, with these things commented out I do at least get the words "Report printed on" in the footer, which is a start!

Sorry to be so dense about this but as I have said before (and I will say again), I am a total novice where footers/ranges etc are concerned. At least I know I can get a page number and date to appear using the earlier code even if it's not exactly high quality programming. http://vbaexpress.com/forum/images/smilies/045.gif

[MOS MASTER]

Hi and welcome to VBAX! :hi:

Modified a tiny, tiny bit...this works over here:
Sub test()
Dim oRange As Word.Range
Dim worddocument As Word.Document 'for testing

Set worddocument = ActiveDocument 'for testing

Set oRange = worddocument.Sections(1).Footers(wdHeaderFooterPrimary).Range
With oRange
.ParagraphFormat.Alignment = 1
.Text = "Report printed on " & FormatDateTime(Now, vbShortDate) & vbTab
.Collapse direction:=wdCollapseEnd

.InsertAfter Text:="Page X of Y"
.InsertAutoText
.Fields.Update
.WholeStory
With .Font
.Size = 10
.Name = "arial"
End With
End With
Set oRange = Nothing
End Sub


HTH, :whistle:

[muttleee]

Hi :hi:

Thanks to all for your help. I got it sorted in the end. The problem was that there seem to be a few subtle differences between regular VBA and Lotusscript, which were causing very simple errors. For example, the line .InsertAfter Text:="Page X of Y" kept giving errors but when I changed it to .InsertAfter "Page X of Y" , it worked fine.

Thanks again! :thumb

[MOS MASTER]

Glad to see you've fixed it!

Strange however Lotus Script doesn't allow fully qualified coding...but if it works ... it works! :moosegrin