Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA How to set the header and footers of an excel worksheet
Can anyone tell me the syntax to set the header and footer values on a
worksheet using VBA? Thanks in advance. John Baker |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA How to set the header and footers of an excel worksheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA How to set the header and footers of an excel worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA How to set the header and footers of an excel worksheet
Try changing
Worksheets("")... to just Sheets("")... As a first guess... "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA How to set the header and footers of an excel worksheet
Thanks so much Jim.
Your code worked a treat. Regards John "Jim Thomlinson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Header & Footers in Templates - Assigning workbook specific | Excel Discussion (Misc queries) | |||
Header/Footers | Excel Worksheet Functions | |||
How to Copy header/footers in Excel 2003 | Excel Discussion (Misc queries) | |||
Header and Footers | Excel Discussion (Misc queries) | |||
Repeating header/footers for excel | Excel Worksheet Functions |