Using VBA to update Page x of y in footers

[murfeezlaw]

I could really use some help here. I'm using VBA in Access to open Word and dynamically create a document. At the end of this document I need to insert more documents. That is working fine. My problem is with the page numbers in the footers. The documents being inserted all have page numbers in the document. I programmatically added a section break after each of the documents being inserted but the code in the footers is as follows: Page { PAGE \*MERGEFORMAT } of {NUMPAGES \*MERGEFORMAT} I need it to count sections not pages

i think i need to programatically change the code at the end of all these documents. i tried this code...
Dim s As Section
Dim f As Field
Dim fcode As Range
For Each s In ActiveDocument.Sections
With s.Headers(wdHeaderFooterPrimary).PageNumbers
.RestartNumberingAtSection = True
.StartingNumber = 1
End With
With s.Footers(wdHeaderFooterPrimary).Range
For Each f In .Fields
If f.Type = wdFieldNumPages Then
Set fcode = f.Code
fcode.Text = "SECTIONPAGES"
End If
Next f
.Fields.Update
End With
Next s

But I get the Method or data member not found error. When i inserted the code in a macro in Word it ran fine. I was just wondering if i was missing a Reference or what could cause that error.

i also tried...
For J = 1 To ActiveDocument.Sections.Count
For K = 1 To ActiveDocument.Sections(J).Headers.Count
objWord.ActiveDocument.Sections(J).Headers(K).PageNumbers.RestartNumberingAtSection = True
objWord.ActiveDocument.Sections(J).Headers(K).PageNumbers.StartingNumber = 1
Next K
For K = 1 To ActiveDocument.Sections(J).Footers.Count
If Field.Type = wdFieldNumPages Then
Set fcode = f.Code
fcode.Text = "SECTIONPAGES"
End If
.Fields.Update
Next K
Next J

but only the first part worked. does anyone have any ideas on this? i've been working on this forever. i'd really appreciate some ideas. With section breaks i need the page numbers to read Page x of y, starting at Page 1 on each new page.

TIA
Ted

 

[PHV]

Replace this:
ActiveDocument.Sections
with this:
objWord.ActiveDocument.Sections

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[murfeezlaw]

Thanks, i tried that. i'm still getting Method or data member not found at:

With s.Headers(wdHeaderFooterPrimary).PageNumbers

any ideas on that?

 

[murfeezlaw]

Thanks, i tried that. i'm still getting Method or data member not found at:

With s.Headers(wdHeaderFooterPrimary).PageNumbers

any ideas?

 

[MakeItSo]

Yes.
Correct your code to this:

Dim s As Section
Dim f As [b]Word.[/b]Field
Dim fcode As [b]Word.[/b]Range
[b]Dim objWrd as Word.Application[/b]

For Each s In objWrd.ActiveDocument.Sections
...

Always specify what you are working with.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Oooooooops!
Also:
Dim s As Word.Section

of course...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[murfeezlaw]

AWESOME! thank you so much! Big help. The only problem i'm having and hopefully this isn't a big deal. it isn't correcting the footer to read Page 1 of SECTIONPAGES for all footers. After looking at it the only time they aren't getting updated is when the footer displays as "First Page Footer" instead of "Footer".

i'm thinking i need to alter this line of code a bit?
With s.Footers(wdHeaderFooterPrimary).Range

Thanks for all the help! and keeping me sane

 

[murfeezlaw]

I think i figured out how to update the footers for the First Page Footer. i basically repeated the code. So I have...(I declared objWord as Word.Application earlier in my code)

Dim s As Word.Section
Dim f As Word.Field
Dim fcode As Word.Range
For Each s In objWord.ActiveDocument.Sections
With s.Headers(wdHeaderFooterPrimary).PageNumbers
.RestartNumberingAtSection = True
.StartingNumber = 1
End With
With s.Footers(wdHeaderFooterPrimary).Range
For Each f In .Fields
If f.Type = wdFieldNumPages Then
Set fcode = f.Code
fcode.Text = "SECTIONPAGES"
End If
Next f
.Fields.Update
End With
With s.Footers(wdHeaderFooterFirstPage).Range
For Each f In .Fields
If f.Type = wdFieldNumPages Then
Set fcode = f.Code
fcode.Text = "SECTIONPAGES"
End If
Next f
.Fields.Update
End With
Next s

it looks to be working but the formatting of the pages is getting a little messed up with the columns and the form titles in the headers. with a section break shouldn't the formatting of each document be preserved?

also how can i add code to insert a blank page after the document being inserted if the document has an odd number of pages?

here is my InsertFile code

For Each ctrl In Me![sfmStateForms].Form.Controls
If TypeOf ctrl Is CheckBox And ctrl.Tag = "Mandatory" Then
If ctrl.Value = True Then
objWord.Selection.InsertFile "L:\Forms\" & Right(ctrl.Name, Len(ctrl.Name) - 3) & ".doc"
objWord.Selection.EndKey Unit:=wdStory
objWord.Selection.InsertBreak Type:=wdSectionBreakNextPage
End If
End If
Next

Thanks!