MS Access VBA to insert Page Numbers into Excel with Read-Only display
Posted on 2013-06-11
I am in the process of adding Page Numbers into an Excel workbook and when doing so, an error message appears that the workbook is read-only.
I attempted to correct by closing the workbook before opening; however, that was not successful.
Please advise as the debug feature highlights, “xlBook.Save”.
Private Sub WrkBKPages_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
Dim ws As Worksheet
Set xlApp = CreateObject("Excel.Application")
Set rs = CurrentDb.OpenRecordset("TblReports")
xlApp.DisplayAlerts = False
'Set reference to Workbook object
xlApp.EnableEvents = False
Set xlBook = xlApp.Workbooks.Open("H:\MasterReport.xls") 'S:\Finance\Finance Shared\CFIDIR\ANALYSIS\Close Reports\MasterReport.xls
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterFooter = "Page # &P"
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
MsgBox "The Workbook has Page Numbers!"