VBA How to set the header and footers of an excel worksheet
On a quick and dirty check it looks like this might work for you...
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
'Set reference to Application object
Set xlApp = CreateObject("Excel.Application")
Dim varExcelFileLocation As String
varExcelFileLocation = GetWordDir
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open("C:\Test")
'Unhide the workbook.
xlApp.Windows(1).Visible = True
varCentreFooter = " Job # " & Forms!frmInquiry.[InquiryID] & "
"" & Forms!frmInquiry.[Client Company]"
'Set worksheet reference...
Set xlSheet = xlBook.Sheets("Job # - Insured Surname")
xlSheet.PageSetup.CenterFooter = varCentreFooter
xlBook.Save
xlBook.Application.Visible = True
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
I assumes that the sheet in question exists. You can check for is nothing on
the sheet object, or better yet in the source fiel you can set the code name
for the sheet. Right now it will be something terribly descriptive like
Sheet1. You can change it in the properties window to something more
appropriate like shtInsuredSurname at which point you could change the code
to
Set xlSheet = shtInsuredSurname
End users of this can not change the code name of the sheet directly the
same way they can change the tab name so your code is less likely to crash...
HTH
I am leaving for the day so if you have any more questions for me
specifically you will have to catch me tomorrow...
"John Baker" wrote:
Thanks Jim
I am using this code from access and it works once but then I get the
following error when I run the code again with a different client :
"Method 'Worksheets of object '_Global' failed"
Any idea why?
The code I have is:
Start Code -------------------------------------------------------
Dim ExcelApp As New Excel.Application
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim varInquiryID As Long
Dim varCentreFooter As String
'Set reference to Application object
Set xlApp = CreateObject("Excel.Application")
Dim varExcelFileLocation As String
varExcelFileLocation = GetWordDir
'Set reference to Workbook object
Set xlBook = xlApp.Workbooks.Open(DestinationFile)
'Unhide the workbook.
xlApp.Windows(1).Visible = True
varCentreFooter = " Job # " & Forms!frmInquiry.[InquiryID] & "
" & Forms!frmInquiry.[Client Company]
Worksheets("Job # - Insured Surname").PageSetup.CenterFooter =
varCentreFooter
xlBook.Save
xlBook.Application.Visible = True
Set xlBook = Nothing
Set xlApp = Nothing
End Code -------------------------------------------------------
"Jim Thomlinson" wrote in message
...
Sub HeaderFooter()
Sheet1.PageSetup.LeftHeader = "Howdy"
Sheet1.PageSetup.CenterHeader = "Do"
Sheet1.PageSetup.RightHeader = "Y'all"
Sheet1.PageSetup.LeftFooter = Application.Path
Sheet1.PageSetup.CenterFooter = Date
Sheet1.PageSetup.RightFooter = Environ("UserName")
End Sub
"John Baker" wrote:
Can anyone tell me the syntax to set the header and footer values on a
worksheet using VBA?
Thanks in advance.
John Baker
|