Include footer page numbers into Excel using MS Access VBA
Posted on 2013-05-23
Your assistance is needed regarding how to include footer page numbers into Excel using MS Access VBA.
The goal is to update an Excel file with MS Access VBA statements. The code below is my attempt to update 53 worksheets in a single workbook. I have been able to manually update the spreadsheet by adding the footer; however, the process should ultimately be automated.
Private Sub MasterUpdate_Click()
Dim ExcelApp As New Excel.Application
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim varCentreFooter As String
Dim WS As Worksheet
Dim rs As Recordset
Dim SSTab As String
Dim shtName As String
Set xlApp = CreateObject("Excel.Application")
Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
xlApp.DisplayAlerts = False
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("H:\PDF\MasterReporttest.xls")
Do While Not (rs.EOF)
shtName = rs!SSTab
Set xlSheet = xlBook.Sheet(shtName)
varCentreFooter = " Page # "
xlSheet.PageSetup.CenterFooter = varCentreFooter
MsgBox "The Workbook is ready!"