Solved

Include footer page numbers into Excel using MS Access VBA

Posted on 2013-05-23
10
769 Views
Last Modified: 2013-05-29
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.Application")

Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
rs.MoveFirst

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

  rs.MoveNext
Loop

MsgBox "The Workbook is ready!"

End Sub
0
Comment
Question by:CFMI
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39192487
I may be wrong here, so please wait for an Excel expert.

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
0
 
LVL 81

Expert Comment

by:byundt
ID: 39192994
The following statement should add the page number to your center footer. &P is the code for the page number.
 varCentreFooter  = "Page # &P"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39193091
Hey Brad...
;-)

guess I misunderstood the question
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Comment

by:CFMI
ID: 39201584
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.Application")

Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
rs.MoveFirst

xlApp.DisplayAlerts = False
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("H:\PDF\Master.xls")

Do While Not (rs.EOF)
    shtName = rs!SSTab
    'Set xlSheet = xlBook.Sheet(shtName)
  xlBook.Activate
  varCentreFooter = "Page # &P"
 
'xlSheet.PageSetup.CenterFooter = varCentreFooter

  rs.MoveNext
Loop

    xlBook.Save
    xlBook.Close
    ExcelApp.Quit
    xlApp.Quit
   
MsgBox "The Workbook is ready!"
ExcelApp.Visible = True

End Sub
0
 
LVL 81

Expert Comment

by:byundt
ID: 39201860
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.

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

Open in new window

0
 
LVL 1

Author Comment

by:CFMI
ID: 39202612
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?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39202639
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
0
 
LVL 1

Author Comment

by:CFMI
ID: 39204290
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"?
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39204454
What type of files (.xls, .xlsx, .xlsb, .xlsm) did you test?
What operating system are you using?
What is your default printer? I found an old KnowledgeBase article saying that PageSetup would fail if there was no default printer.

The following code works if run from Excel. It puts the page number in the center footer of every worksheet except for two as listed in a Select Case block:
Sub CenterFooter()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
    Case "Summary", "Confidential"  'Do nothing to footers on these worksheets
    Case Else
        ws.PageSetup.CenterFooter = "Page # &P"
    End Select
Next
End Sub

Open in new window

Brad
0
 
LVL 1

Author Closing Comment

by:CFMI
ID: 39205413
Brad,

Forget the questions; your code is PERFECT.  Joe himself could not have thrown a better Touchdown pass - THANK you very much!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question