Unanswered: access vba to write excel header and footer
I need to use access vba to write excel header and footer. When I execute the following code, it opened up the excel file. If I did view -> header and footer, I can see the code did write the header and footer. When I try to close the excel, it asked me to save the file. If I did not save the file, the next time I opened it, the header and footer disappeared.
If I comment out the line xlBook.Application.Visible = True, it did not open the excel file and when I open the excel file manually, there is no header and footer.
I need the code to save the header and footer behind the scene without opening it. How to change the code?
Your help will be greatly appreciated!
Private Sub cmdentermain_Click()
Dim ExcelApp As New Excel.Application
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim varInquiryID As Long
Dim varCentreFooter As String
Dim sHeader As String
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("c:\file1.xls")
varCentreFooter = " project # "
Set xlSheet = xlBook.Sheets("file1")
xlSheet.PageSetup.CenterFooter = varCentreFooter