VBA How to set the header and footers of an excel worksheet

[John Baker]

Can anyone tell me the syntax to set the header and footer values on a
worksheet using VBA?

Thanks in advance.

John Baker

 

[Guest]

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]

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 -------------------------------------------------------

 

[Guest]

Try changing

Worksheets("")...

to just

Sheets("")...

As a first guess...

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 -------------------------------------------------------

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

 

[Guest]

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...

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 -------------------------------------------------------

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]

Thanks so much Jim.

Your code worked a treat.

Regards
John

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...

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 -------------------------------------------------------


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


:

Can anyone tell me the syntax to set the header and footer values on a
worksheet using VBA?

Thanks in advance.

John Baker