CFMI
asked on
Include footer page numbers into Excel using MS Access VBA
Hello Experts,
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.Applic ation")
Set rs = CurrentDb.OpenRecordset("T blReportsO rder")
rs.MoveFirst
xlApp.DisplayAlerts = False
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("H:\P DF\MasterR eporttest. xls")
Do While Not (rs.EOF)
shtName = rs!SSTab
Set xlSheet = xlBook.Sheet(shtName)
varCentreFooter = " Page # "
xlSheet.PageSetup.CenterFo oter = varCentreFooter
rs.MoveNext
Loop
MsgBox "The Workbook is ready!"
End Sub
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.Applic
Set rs = CurrentDb.OpenRecordset("T
rs.MoveFirst
xlApp.DisplayAlerts = False
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("H:\P
Do While Not (rs.EOF)
shtName = rs!SSTab
Set xlSheet = xlBook.Sheet(shtName)
varCentreFooter = " Page # "
xlSheet.PageSetup.CenterFo
rs.MoveNext
Loop
MsgBox "The Workbook is ready!"
End Sub
The following statement should add the page number to your center footer. &P is the code for the page number.
varCentreFooter = "Page # &P"
varCentreFooter = "Page # &P"
Hey Brad...
;-)
guess I misunderstood the question
;-)
guess I misunderstood the question
ASKER
Hello,
When I updated the statement, the footer wasn't updated so I was thinking could it be a problem using a xls instead of the xlsx file? Any other help with the below code is really appreciated as I am looping through an Access table with the Worksheet names and opening the workbook. As you see, I tried opening the workbook at the end since I have to reboot to open the file.
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.Applic ation")
Set rs = CurrentDb.OpenRecordset("T blReportsO rder")
rs.MoveFirst
xlApp.DisplayAlerts = False
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("H:\P DF\Master. xls")
Do While Not (rs.EOF)
shtName = rs!SSTab
'Set xlSheet = xlBook.Sheet(shtName)
xlBook.Activate
varCentreFooter = "Page # &P"
'xlSheet.PageSetup.CenterF ooter = varCentreFooter
rs.MoveNext
Loop
xlBook.Save
xlBook.Close
ExcelApp.Quit
xlApp.Quit
MsgBox "The Workbook is ready!"
ExcelApp.Visible = True
End Sub
When I updated the statement, the footer wasn't updated so I was thinking could it be a problem using a xls instead of the xlsx file? Any other help with the below code is really appreciated as I am looping through an Access table with the Worksheet names and opening the workbook. As you see, I tried opening the workbook at the end since I have to reboot to open the file.
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.Applic
Set rs = CurrentDb.OpenRecordset("T
rs.MoveFirst
xlApp.DisplayAlerts = False
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("H:\P
Do While Not (rs.EOF)
shtName = rs!SSTab
'Set xlSheet = xlBook.Sheet(shtName)
xlBook.Activate
varCentreFooter = "Page # &P"
'xlSheet.PageSetup.CenterF
rs.MoveNext
Loop
xlBook.Save
xlBook.Close
ExcelApp.Quit
xlApp.Quit
MsgBox "The Workbook is ready!"
ExcelApp.Visible = True
End Sub
The PageSetup.CenterFooter part works perfectly in Excel. In Word, it worked on .xls and .xlsx files, but not on a .xlsm file. There were no errors, but the .xlsm file just didn't save.
I changed your code to use late-binding so you don't need to set references.
I changed your code to use late-binding so you don't need to set references.
Private Sub MasterUpdate_Click()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim varCentreFooter As String
Dim rs As Recordset
Dim SSTab As String
Dim shtName As String
Set xlApp = CreateObject("Excel.Application")
Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
rs.MoveFirst
xlApp.DisplayAlerts = False
'Set reference to Workbook object
xlApp.EnableEvents = False
Set xlBook = xlApp.Workbooks.Open("H:\PDF\Master.xls")
Do While Not (rs.EOF)
shtName = rs!SSTab
Set xlSheet = xlBook.Worksheets(shtName)
varCentreFooter = "Page # &P"
xlSheet.PageSetup.CenterFooter = varCentreFooter
rs.MoveNext
Loop
xlBook.Save
xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
MsgBox "The Workbook is ready!"
End Sub
ASKER
Experts,
When I used the above code, there were no footers. The code ran but afterwards, the spreadsheet did not have any footers so I tried stepping through the code and it also appeared to work well. Do you have any other ideas or perhaps a different direction?
When I used the above code, there were no footers. The code ran but afterwards, the spreadsheet did not have any footers so I tried stepping through the code and it also appeared to work well. Do you have any other ideas or perhaps a different direction?
When I stepped through the code, I could verify (using the Immediate pane) that the footer was updated on a .xlsm file--but the save never completed as evidenced by the file modification date. I tried early-binding, late-binding, workbook activation and sheet activation. None of it seemed to make a difference.
I did enjoy success using Word 2013 (I don't use Access) to run the macro without the Access-specific stuff, but only on .xls and .xlsx files. My failure with .xlsm files could be due to corruption in my Excel 2013 install or it could be an actual bug.
When you tested the code in Access, which version did you use? Did the file modification date change?
If both of us are having problems, it makes it more likely that there is a bug. If so, I'll report it to Microsoft.
Brad
I did enjoy success using Word 2013 (I don't use Access) to run the macro without the Access-specific stuff, but only on .xls and .xlsx files. My failure with .xlsm files could be due to corruption in my Excel 2013 install or it could be an actual bug.
When you tested the code in Access, which version did you use? Did the file modification date change?
If both of us are having problems, it makes it more likely that there is a bug. If so, I'll report it to Microsoft.
Brad
ASKER
Good Morning Brad,
I am using Access 2010 v 14.0.6112.5000 (32-bit) and the modification date did not change so I will inform Microsoft; should I select "Accept as Solution"?
I am using Access 2010 v 14.0.6112.5000 (32-bit) and the modification date did not change so I will inform Microsoft; should I select "Accept as Solution"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brad,
Forget the questions; your code is PERFECT. Joe himself could not have thrown a better Touchdown pass - THANK you very much!
Forget the questions; your code is PERFECT. Joe himself could not have thrown a better Touchdown pass - THANK you very much!
It may be difficult to determine the Page numbering from Access.
The Page numbering is done from within the Excel environment, based on the Page settings: margins, orientation, Papersize, header size, footer size, ...etc
So AFAIK, Excel will do this automatically.
If you "hardcode" the page numbering, it may be wrong if any of the settings, I mentioned above, are changed
JeffCoachman