Access 2010 executing Word VBA: Page X of Y AutoText entry in Header.... would like to replace this code

Hi EE experts,

Am revisiting a post from last year with a different question because now want to run under Windows 7/Access 2010 as mdb.

The post from last year is here:

http://www.experts-exchange.com/Database/MS_Access/Q_28461432.html?searchSuccess=true&searchTop10=true

The topic is about executing from Access a Word mail merge using an Excel file as the data source.

Below attached is a zip file with the:  1) .mdb
                                                                       2)  excel input file
                                                                      3)   word mail merge template file


Today the question is about Page X OF Y.

This autotext entry does not exist in our Word 2010 installation.
Therefore the code to place Page X of Y in the Word Header VBA is aborting with error, that this item is not found in the collection.

Truthfully, I google for VBA code and cut and paste alot.... so I have gaps in my knowledge.

I found this that I cold use to replace using Autotext entry.

http://www.java2s.com/Code/VBA-Excel-Access-Word/Word/CreatingPageXofYPageNumbers.htm

They have code to do page numbering instead of Auto text, am copying the code below:

Sub pageNumber()
    ActiveDocument.Sections(ActiveDocument.Sections.Count) _
        .Headers(wdHeaderFooterPrimary).Range.Select
    With Selection
        .Paragraphs(1).Alignment = wdAlignParagraphCenter
        .TypeText Text:="Page "
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
            "PAGE ", PreserveFormatting:=True
        .TypeText Text:=" of "
        .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
            "NUMPAGES ", PreserveFormatting:=True
    End With
End Sub



Can you please help me place this code in my Access module.

I tried, but getting error msg: Obect vaiable or with block variable not set.

Am uploading a zip file ... please un on Windows 7/Access 2010/Word 2010

1) mdb file

        I removed forms etc  from mdb to keep it simple.
        Please go to module:  500_word_mail_merge
        Please        execute:      sub_test
2) excel data file
3)word template file


The logic takes the whole merge file, and subdivides into smaller docs per each student (an EE  expert helped me with the subdividing logic in the past)

4) 3 sample student files that were subdivided from the big merge document:
       1) Thomas Jefferson
       2) Abraham Lincoln
      3) George Washington

5) An xp version of (4) where you see how would like the header to look like:

nothing on the first page
the each subsequent page to have in the header Page n of o        Re: Student's name

 If you can only get the logic to work if you do place on the first page header, too.... that would be fine.

** At this time I could not upload the zip file bec getting msg: The archive could not be scanned stream closed.

      The zip file from last year in the link listed at the top of this post could be used.

      This is the new code I am trying to add that's giving me an error.

         Set myRange = appWord.ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range
'       With myRange
'            ' put page number in header
'                   appWord.NormalTemplate.AutoTextEntries("PAGE x of y").Insert _
'                   Where:=myRange, _
'                   RichText:=True
'                  .Move Unit:=wdstory, Count:=1
'            ' put student name in header
'                  .InsertAfter "          " & strHeaderText
'
'       End With
       
       

       With myRange
          With appWord.Selection
             'put page number in header
            .Paragraphs(1).Alignment = wdAlignParagraphCenter
            .TypeText Text:="Page: "

            .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
                "PAGE ", PreserveFormatting:=True

            .TypeText Text:=" of "

            .Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
                "NUMPAGES ", PreserveFormatting:=True


             '     .Move Unit:=wdstory, Count:=1

           ' put student name in header
                  .InsertAfter "          " & strHeaderText
 
           End With
       End With

Thx so much for any help you can provide,
sandra
mytfeinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mytfeinAuthor Commented:
mytfeinAuthor Commented:
mytfeinAuthor Commented:
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

mytfeinAuthor Commented:
Nick67Commented:
Friggin' Ribbon :(
This autotext entry does not exist in our Word 2010 installation.
Since it does exist in Word 2013, it is very likely that it is still there in 2010.
And I checked, and it is.
But it has been brutally hidden.
The Insert Ribbon has a Page Number dropdown.
Choose where you want Page X of Y
Now there look to be a bazillion formats
Look VERY CAREFULLY at each item
They start with Simple
and then Page X
And then you'll see that somewhere along the way they switch to formats for PAGE X of Y

So your initial premise is wrong.
Does that change what you want to do?
mytfeinAuthor Commented:
Hi Nick,

tx for replying,

Do not want to use the AutoText anymore....

found another sample how to code the page numbers using VBA

it works better, but still having problems

this is the code snippet so far:

 With appWord.ActiveDocument.Sections(1)
            Set myRange = .Headers(wdHeaderFooterPrimary).Range
           
            myRange.InsertAfter Text:="Page: "
           
'            myRange.Collapse Direction:=wdCollapseEnd
            myRange.Fields.Add Range:=myRange, Type:=wdFieldPage
           
            myRange.InsertAfter Text:=" of "
           
                     
'            myRange.Collapse Direction:=wdCollapseEnd
            myRange.Fields.Add Range:=myRange, Type:=wdFieldNumPages
       End With

I was inspired by:

http://windowssecrets.com/forums/showthread.php/114424-VBA-Word-Footer-(VBA-Word-2007)


I was about to google what collapse does, bec it's coming out as Page: of 52.... it should be Page: 2 of 5

Below is a screen shot of what header looks like with the above code,  it should be Page: 2 of 5
Header-pages.bmp
mytfeinAuthor Commented:
screen shot retry
mytfeinAuthor Commented:
When I added code to place Student's name, it got more jumbled in the header

 With appWord.ActiveDocument.Sections(1)
            Set myRange = .Headers(wdHeaderFooterPrimary).Range
           
            myRange.InsertAfter Text:="Page: "
           
            myRange.Collapse Direction:=wdCollapseEnd
            myRange.Fields.Add Range:=myRange, Type:=wdFieldPage
           
            myRange.InsertAfter Text:=" of "
           
                     
            myRange.Collapse Direction:=wdCollapseEnd
            myRange.Fields.Add Range:=myRange, Type:=wdFieldNumPages
           
            myRange.InsertAfter Text:=strHeaderText
       End With



Header-pages2.bmp

I do not know how to control the placement of the fields....  If you have any info how to do, would greatly appreciate....

will google in the meantime... tx,
sandra
mytfeinAuthor Commented:
Hi Nick,

Found this link, and followed the recommendations.....

It works for me now....

It's just not clear to me why have to do collapse to the end
       and the link below, he recommends after adding a field, to set the range again before adding another field

 ' http://stackoverflow.com/questions/14747261/inserting-text-and-fields-in-word-2010-header-without-using-select
       
       With appWord.ActiveDocument.Sections(1)
            Set myRange = .Headers(wdHeaderFooterPrimary).Range
           
            myRange.InsertAfter Text:="Page: "
            myRange.Collapse Direction:=wdCollapseEnd
            myRange.InsertAfter Text:=" "
            myRange.Collapse Direction:=wdCollapseEnd
           
            myRange.Fields.Add Range:=myRange, Type:=wdFieldPage
            myRange.Collapse Direction:=wdCollapseEnd
            myRange.InsertAfter Text:=" "
            myRange.Collapse Direction:=wdCollapseEnd
           
            Set myRange = .Headers(wdHeaderFooterPrimary).Range
           
            myRange.InsertAfter Text:=" of "
            myRange.Collapse Direction:=wdCollapseEnd
            myRange.InsertAfter Text:=" "
            myRange.Collapse Direction:=wdCollapseEnd
           
           
            myRange.Fields.Add Range:=myRange, Type:=wdFieldNumPages
            myRange.Collapse Direction:=wdCollapseEnd
            myRange.InsertAfter Text:=" "
            myRange.Collapse Direction:=wdCollapseEnd
           
            Set myRange = .Headers(wdHeaderFooterPrimary).Range
           
            myRange.InsertAfter Text:="       " & strHeaderText
            myRange.Collapse Direction:=wdCollapseEnd
           
            myRange.Fields.Update
       End With





Below  is the screen shot....  creating pages in header without using the autotextentry.... using more vba code....

Header-pages3.bmp
Nick67Commented:
WOW!
MS should be shot!
This nice code works in Word 2003
ActiveWindow.View.SplitSpecial = wdPaneNone
ActiveWindow.ActivePane.View.Type = wdPrintView
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
NormalTemplate.AutoTextEntries("Page X of Y").Insert Where:=Selection.Range, RichText:=True

Open in new window


This is it's Word 2013 replacement

ActiveWindow.View.SplitSpecial = wdPaneNone
ActiveWindow.ActivePane.View.Type = wdPrintView
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Application.Templates( _
        "C:\Users\username\AppData\Roaming\Microsoft\Document Building Blocks\1033\15\Built-In Building Blocks.dotx" _
        ).BuildingBlockEntries("Bold Numbers 1").Insert Where:=Selection.Range, RichText:=True

Just WOW!

Ok, this works in Word
ActiveWindow.View.SplitSpecial = wdPaneNone
ActiveWindow.ActivePane.View.Type = wdPrintView
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.TypeText Text:="Page "
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

Open in new window


Does that help
mytfeinAuthor Commented:
Hi Nick,

Yes you are right when you mentioned before that the AutoText is in the ribbon, with a gazillion variations.

But, I am rusty and new to Office 2010 so working with it felt overwhelming.

Another website mentioned, if you delete the Normal.dot (now Normal.dotm?), it can pull it in.... but when I discovered that the Normal.dot sits in each person's individual user folder, preferred to create the page number using vba code.

Question:
Did you simply turn on the macro recorder, and pick a choice from one of the gazillion page options, and then copy the code(bec i forgot that approach)  ?


I get into trouble when I used the vba behind the macro recorder. Because I do not know how to accurately defie object in Access, so that it does not tie up memory on the next iteration of running the code, which is what you solved last year.

So it's almost the end of the day here, so will try your technique tommorrow.
I may or might not have success with transforming it for Access vba module.

Will be in touch....

I loved  Access 97, 2000, 2003.....   :-)    

tx, sandra
Nick67Commented:
Did you simply turn on the macro recorder, and pick a choice from one of the gazillion page options, and then copy the code(bec i forgot that approach)  ?
<grin>I am an Access guy -- but ABSOLUTELY that's how I get the bones of any automation code I need for Word/Excel</grin>

And yes, sometimes it is not so simple to get things moving from the macro code to using Access
WordApp.whatever.whatever
But once you get some code going, you comment it and come back to it the next time out.
And the macro code has all the keyword to throw in with 'ms access vba ...' google searches :)

In this case it wasn't bad
Sample attached
Git 'er dun!
word-pages.mdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mytfeinAuthor Commented:
Hi Nick,

Totally agree and understand what you wrote above about word/excel vba and google is one's friend :-)

I tested the mdb that you graciously provided and it works!

The things is, in the past my user does not want a header on first page,  just subsequent pages which the vba that i posted yesterday does....

I have to ask user if header on first page is ok....

I was told that on Jun 1 work is cutting off XP pcs from the network. My users are remoting in from their Windows 7 to an XP pc to use my Access 2003 system.  I am testing the mdbs on A2010 and am finding some issues. Most of testing is done....

So hope it's ok with you if i retain the logic that works from yesterday so that I can continue testing.

(I see already the next bug for the same mail merge project that I need to post again)

thx soooo much for your help, always learning something new :-)

sandra
mytfeinAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Nick67's comment #a40799269
Assisted answer: 0 points for mytfein's comment #a40799112

for the following reason:

The code that I created using various websites as inspiration is another approach to headers ( a little clunky, but let's you control if you do not want a header on first page)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.