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