PDA

View Full Version : Excel Macro to Add Footer Text and Page Numbers to Word



mstar
01-28-2015, 02:01 PM
Hi all,

I've got a macro I'm using that copies a number of Excel ranges and pastes them into a new Word document. When finished, the document should have three sections with page numbers (aligned right) throughout and a footer (aligned left) only in section 2. Here's what I've developed so far (sorry - it's likely not the cleanest code):

Section 1

With wrdApp.ActiveDocument.Sections(1)
.Footers(wdHeaderFooterPrimary).PageNumbers.Add wdAlignPageNumberRight
.Footers(wdHeaderFooterPrimary).PageNumbers.ShowFirstPageNumber = False
.Footers(wdHeaderFooterPrimary).PageNumbers.RestartNumberingAtSection = True
.Footers(wdHeaderFooterPrimary).PageNumbers.StartingNumber = 0
End With
Section 2


wrdApp.Selection.InsertBreak Type:=wdSectionBreakNextPage
With wrdApp.ActiveDocument.Sections(2)
.Footers(wdHeaderFooterPrimary).LinkToPrevious = False
.Footers(wdHeaderFooterPrimary).Range.InsertBefore "Note: * significant for p<.05; Cohen’s D effect size are: “-“ for <.2 “+” for .2 - .49 “++” for .5 - .79 “+++” for >.8"
.Footers(wdHeaderFooterPrimary).PageNumbers.Add wdAlignPageNumberRight
.Footers(wdHeaderFooterPrimary).PageNumbers.ShowFirstPageNumber = True
.Footers(wdHeaderFooterPrimary).PageNumbers.RestartNumberingAtSection = False
End With
Section 3


wrdApp.Selection.InsertBreak Type:=wdSectionBreakNextPage
With wrdApp.ActiveDocument.Sections(3)
.Footers(wdHeaderFooterPrimary).LinkToPrevious = False
.Footers(wdHeaderFooterPrimary).PageNumbers.Add wdAlignPageNumberRight
.Footers(wdHeaderFooterPrimary).PageNumbers.ShowFirstPageNumber = True
.Footers(wdHeaderFooterPrimary).PageNumbers.RestartNumberingAtSection = False
End With

With this code, the page numbers get inserted throughout the document (right justified) and they number correctly. The section 2 footer text is also added, but it is added right justified as well. I tried inserting the section 2 footer text in section 1 before the alignment code just to see if it would work, and it did... that leads me to believe it's a formatting issue. Once the page numbering format is set to be right aligned, it keeps it there.

Any thoughts on how I can fix this? If you need more information or add'l code from me, let me know. I'm still pretty new at this, and I appreciate any suggestions you might have.