How Can I Change the Footer in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I change the footer in an Excel spreadsheet?

— JA

Hey, JA. Ah, a trick question, huh? As it turns out, there’s no such thing as the footer in Excel; instead, Excel has three different footers: the left footer, the center footer, and the right footer. And here’s a script that can change all three of them:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.PageSetup.LeftFooter = “Left footer”
objWorksheet.PageSetup.CenterFooter = “Center footer”
objWorksheet.PageSetup.RightFooter = “Right footer”

As you can see, there really isn’t all that much to this script. We begin by creating an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We use the Add method to add a new workbook, then use this line of code to bind to the first worksheet in the Worksheets collection:

Set objWorksheet = objWorkbook.Worksheets(1)

All we have to do now is assign values to LeftFooter, CenterFooter, and RightFooter, all of which happen to be properties of the worksheet’s PageSetup object:

objWorksheet.PageSetup.LeftFooter = “Left footer”
objWorksheet.PageSetup.CenterFooter = “Center footer”
objWorksheet.PageSetup.RightFooter = “Right footer”

Cool.

OK, that’s a good point: it’s possible that you don’t want your center footer to simply read “Center Footer.” Obviously you can assign any text you want to these footers. In addition, you can also include some of Excel’s special formatting codes within a footer. For example, here’s a script that uses the current date as the center footer:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.PageSetup.CenterFooter = “&D”

There’s really nothing to it: we simply use the formatting code &D to insert the date. (Note that the formatting code must be enclosed in quotation marks, just like any other string value.)

Here’s a slightly fancier footer, one that gives you a Page 1 of 9 sort of thing. Notice that we can mix both regular old text and the special formatting codes in the same string value:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.PageSetup.CenterFooter = “Page &P of &N”

You can see the value we assigned to the footer: Page &P of &N. That simply means “Put the following in the footer: the word Page, followed by the current page number (&P) followed by the word of, followed by the total number of pages in the spreadsheet (&N).” Child’s play.

And now we have a trick question for you: If you spell “sit all day in the tub” S-O-A-K, and you spell “a funny story” J-O-K-E, how do you spell “the white of an egg?”

Nope, sorry: the white of an egg is spelled A-L-B-U-M-E-N. (Thank goodness you asked us an Excel footer question instead of a trick question like that.)

Editor’s Note: You might be thinking that the editor should have stepped in and saved you from such a bad joke, but sometimes it’s easier to let the Scripting Guys (okay, Greg) think they’re (he’s) funny. You can just laugh politely and walk away like this editor tends to do.

ScriptingGuy1

Follow