How Can I Change the Footer in an Excel Spreadsheet?

Hey, Scripting Guy! Question

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

— JA

Spacer Hey, Scripting Guy! Answer Script Center

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:


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:


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:


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:


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:


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.

Follow    

No Comment.