Help needed with footerfor word from excel VBA

[gigahacker]

I am new at VBA, my code is very dirty however it works and my project is 99 percent complete. My project is to take a bunch of different size excel tabs and have them print to one PDF. After a lot of research I found that this is almost impossible so I decided that I would copy ranges from each tab to word and print as one big formatted document. This works prefect however the code I found on the internet for my footer to display, works but I do not understand it enough to modify it. Below I will post what works, what I can't get to work.


<code>'add footer to word document With wrdDoc.Sections(1) With .Footers(wdHeaderFooterPrimary) Set rng = .Range.Duplicate rng.Collapse wdCollapseEnd rng.InsertBefore vbTab & "Page of " rng.Collapse wdCollapseStart rng.Move wdCharacter, 6 wrdDoc.Fields.Add rng, wdFieldPage Set rng = .Range.Duplicate rng.Collapse wdCollapseEnd wrdDoc.Fields.Add rng, wdFieldNumPages End With End With

</code>This is what I recorded using macro recorder however its from within the temp word file and not from excel write to the temp word file.

What I need it to look like: on right footer: Printed &[Date] &[Time]
on left footer: CA4-cs-060 Gas Divider Calibration Certificate
Originated : 10-6-2009 Revised: 11-6-12
Page &[Page] of &[Pages]

Code from recording (this does not work from the excel VBA):

<code> With wrdDoc.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "Printed &D &T" .CenterFooter = "" .RightFooter = _ "CA4-cs-060 Gas Divider Calibration Certificate" & Chr(10) & " Originated : 10-6-2009 Revised: 11-6-12" & Chr(10) & " Page &P of &N" .LeftMargin = Application.InchesToPoints(0.7) .RightMargin = Application.InchesToPoints(0.7) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.75) .HeaderMargin = Application.InchesToPoints(0.3) .FooterMargin = Application.InchesToPoints(0.3) .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" .EvenPage.CenterHeader.Text = "" .EvenPage.RightHeader.Text = "" .EvenPage.LeftFooter.Text = "" .EvenPage.CenterFooter.Text = "" .EvenPage.RightFooter.Text = "" .FirstPage.LeftHeader.Text = "" .FirstPage.CenterHeader.Text = "" .FirstPage.RightHeader.Text = "" .FirstPage.LeftFooter.Text = "" .FirstPage.CenterFooter.Text = "" .FirstPage.RightFooter.Text = "" End With

</code>I would like to either use this code or understand how to modify the code that is working...either or, please help.<code></code>