{"id":68283,"date":"2005-12-21T22:33:00","date_gmt":"2005-12-21T22:33:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/12\/21\/how-can-i-change-the-footer-in-an-excel-spreadsheet\/"},"modified":"2005-12-21T22:33:00","modified_gmt":"2005-12-21T22:33:00","slug":"how-can-i-change-the-footer-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-change-the-footer-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Change the Footer in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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

— JA<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.PageSetup.LeftFooter = “Left footer”\nobjWorksheet.PageSetup.CenterFooter = “Center footer”\nobjWorksheet.PageSetup.RightFooter = “Right footer”\n<\/PRE>\n
As you can see, there really isn\u2019t all that much to this script. We begin by creating an instance of the Excel.Application<\/B> object and then set the Visible<\/B> property to True; that gives us a running instance of Excel that we can see onscreen. We use the Add<\/B> method to add a new workbook, then use this line of code to bind to the first worksheet in the Worksheets<\/B> collection:<\/P>
Set objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
All we have to do now is assign values to LeftFooter<\/B>, CenterFooter<\/B>, and RightFooter<\/B>, all of which happen to be properties of the worksheet\u2019s PageSetup<\/B> object:<\/P>
objWorksheet.PageSetup.LeftFooter = “Left footer”\nobjWorksheet.PageSetup.CenterFooter = “Center footer”\nobjWorksheet.PageSetup.RightFooter = “Right footer”\n<\/PRE>\n
Cool.<\/P>\n
OK, that\u2019s a good point: it\u2019s possible that you don\u2019t want your center footer to simply read \u201cCenter Footer.\u201d Obviously you can assign any text you want to these footers. In addition, you can also include some of Excel\u2019s special formatting codes<\/B><\/A> within a footer. For example, here\u2019s a script that uses the current date as the center footer:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.PageSetup.CenterFooter = “&D”\n<\/PRE>\n
There\u2019s really nothing to it: we simply use the formatting code &D<\/B> to insert the date. (Note that the formatting code must be enclosed in quotation marks, just like any other string value.) <\/P>\n
Here\u2019s a slightly fancier footer, one that gives you a Page 1 of 9<\/B> sort of thing. Notice that we can mix both regular old text and the special formatting codes in the same string value:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.PageSetup.CenterFooter = “Page &P of &N”\n<\/PRE>\n
You can see the value we assigned to the footer: Page &P of &N<\/B>. That simply means \u201cPut the following in the footer: the word Page<\/I>, followed by the current page number (&P) followed by the word of<\/I>, followed by the total number of pages in the spreadsheet (&N).\u201d Child\u2019s play.<\/P>\n
And now we have a trick question for you<\/I>: 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?”<\/P>\n
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<\/I>.)<\/P>\n
Editor\u2019s Note: You might be thinking that the editor should have stepped in and saved you from such a bad joke, but sometimes it\u2019s easier to let the Scripting Guys (okay, Greg) think they\u2019re (he\u2019s) funny. You can just laugh politely and walk away like this editor tends to do.<\/I><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s 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\u2019s a script that […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,711,48,49,3,5],"class_list":["post-68283","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019s 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\u2019s a script that […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68283","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=68283"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/68283\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=68283"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=68283"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=68283"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}