MS Access VBA to include headers and footers for a PDF file inside the creation VBA

Hello Experts,

Your assistance is needed regarding how to include a header and page numbers into a created PDF file.  

The goal is to include MS Access VBA statements within the creation, as well.  The code below creates a PDF file by using a Master Workbook with 53 worksheets.  I have been able to manually update the Master spreadsheet by adding the header and footer; however, the process should ultimately be automated.

Perhaps, adding a function to update the worksheet formats before creation would be an option?  For example, make every header for each worksheet the same name as the worksheet tab and all footers has a Page Number.

Private Sub Prepare_PDF_Click()
Dim MyFullName As String
Dim xlAppFTP As Object, xlWb As Object, xlWs As Object
Set xlAppFTP = CreateObject("Excel.Application")
Set xlWb = xlAppFTP.Workbooks.Open("H:\PDF\Master.xls")
MyFullName = "H:\PDF\MonthlyReport.pdf"

xlWb.ExportAsFixedFormat Type:=0, FileName:=MyFullName, _
Quality:=1, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

xlWb.Close
Set xlWs = Nothing
Set xlAppFTP = Nothing
Set xlApp = Nothing
MsgBox "The PDF file was created"

End Sub
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What would your "header" consist of?

In most cases, the PDF software would create page numbers as needed. If you need to add page numbers arbitrarily, you'd have to define the logic for that, and work with that.
0
CFMIFinancial Systems AnalystAuthor Commented:
Experts,

Customer now, doesn't need Headers and the above code is used to create the PDF but that file doesn't include page numbers when the Excel worksheets format doesn't include Page Numbers.  Is there a way to include Page Numbers in the above code or am I stuck manually updating each worksheet?
0
CFMIFinancial Systems AnalystAuthor Commented:
Experts,

Below displays code to update a workbook to include page numbers, but is there a way to also include a Header?  For example, is there "CenterHeader " syntax?

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
        ws.PageSetup.CenterFooter = "Page # &P"
Next
End Sub
0

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
CFMIFinancial Systems AnalystAuthor Commented:
I discovered the code:
Worksheets("Sheet2").PageSetup.CenterHeader = _
"Some&B&""Courier New""&10 Bold Courier New Size 10&B&""Arial"" Text"
0
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 Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.