{"id":54603,"date":"2009-01-15T11:56:00","date_gmt":"2009-01-15T11:56:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/01\/15\/hey-scripting-guy-how-can-i-modify-the-footers-of-an-office-excel-spreadsheet\/"},"modified":"2009-01-15T11:56:00","modified_gmt":"2009-01-15T11:56:00","slug":"hey-scripting-guy-how-can-i-modify-the-footers-of-an-office-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-modify-the-footers-of-an-office-excel-spreadsheet\/","title":{"rendered":"Hey, Scripting Guy! How Can I Modify the Footers of an Office Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/h2>\n

Hey, Scripting Guy! I have a simple question for you: How can I modify the footers in an Office Excel spreadsheet?<\/p>\n

– MN<\/p>\n

\"Spacer\"\"Hey,<\/p>\n

Hi MN,<\/p>\n

I have a simple answer for you as well. You assign a value to the appropriate footer property of the pagesetup<\/b> object. If you would prefer a longer answer, read on.<\/p>\n\n<\/thead>\n\n\n
\n

The Office Excel automation model is documented here on MSDN<\/a>. Here are previous “Hey, Scripting Guy!” articles<\/a> that illustrate using VBScript to automate Microsoft Excel. The Office space archive<\/a> also has numerous examples of using VBScript with Office Excel. You can also find lots of examples of automating Office Excel in the Script Center Repository<\/a>. Perhaps I also saved the best for last. The Community-Submitted Scripts Center has a ton<\/i> of scripts that automate Office Excel. Here is the direct link to that gold mine of information<\/a>. Look in the Microsoft Office section. If you are new to using Microsoft PowerShell, you can get a jump-start on it with the resources in the Windows PowerShell hub<\/a>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Today, we have a script called CreateExcelFooter.ps1<\/b> that creates an instance of the Office Excel application object, makes it visible, adds a workbook, retrieves the worksheet, and then adds three different footers to it. Just for fun, we also show the spreadsheet in print preview fashion. The script is seen here:<\/p>\n

$excel = new-object -comobject excel.application\n$excel.visible = $true\n$workbook = $excel.workbooks.add()\n$worksheet = $workbook.worksheets.item(1)\n$worksheet.pageSetup.LeftFooter = \"Date: &D\"\n$worksheet.pageSetup.CenterFooter = \"Time: &T\"\n$worksheet.pageSetup.RightFooter = \"Page &P of &N\"\n$worksheet.cells.item(1,1) = \"a\"\n$workbook.printPreview()\n<\/pre>\n
The first thing we need to do is to create an instance of the Excel Application<\/b> object. We do this by using the New-Object<\/b> cmdlet and specifying the comobject<\/b> parameter. We store the application object that is returned in the $excel<\/b> variable. This line of code is seen here: <\/p>\n
$excel = new-object -comobject excel.application<\/pre>\n
Next we want to be able to see our Office Excel application, so we make the visible<\/b> property true. In cases in which we want to automate a large number of items and we have our script working properly, I will generally not make Office Excel visible. This allows the script to run and to not tie up things on the desktop. This line of code is seen here: <\/p>\n
$excel.visible = $true<\/pre>\n
After we have the Office Excel application visible, we decide to add a workbook. Workbooks are a key component of Office Excel, and we nearly always need to add a workbook to the script. To do this, we use the workbooks<\/b> property of the application<\/b> object to return a workbooks<\/b> collection, and we use the add<\/b> method. We use the $workbook<\/b> variable to hold the workbook<\/b> object that is created: <\/p>\n
$workbook = $excel.workbooks.add()<\/pre>\n
We now use the worksheets<\/b> property of the workbook<\/b> object to retrieve the first worksheet. We use the item<\/b> method of the worksheets<\/b> object to return the first worksheet. This is seen here:<\/p>\n
$worksheet = $workbook.worksheets.item(1)<\/pre>\n
When we have the worksheet<\/b> object, we can use the pagesetup<\/b> property to return the pagesetup<\/b> object, and then we can access the leftfooter<\/b> property. To change the left footer, you just assign an acceptable value to the property. We use &D<\/b>, which is a formatting code that tells Office Excel to add the date (this is documented here<\/a>): <\/p>\n
$worksheet.pageSetup.LeftFooter = \"Date: &D\"<\/pre>\n
We can do the same thing to the center footer and to the right footer. For the center footer, we use the &T<\/b> formatting code to display the current time. For the right footer, we use the &P<\/b> code to display the current page number, and the &N<\/b> to display the number of pages in the worksheet. You can actually use any of the coded values seen in Table 1 in either the header or in the footer:<\/p>\n
Table 1 Excel format codes used for headers and footers<\/b><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Format code<\/td>\nMeaning<\/td>\n<\/tr>\n<\/thead>\n
\n
&D<\/p>\n<\/td>\n
\n
Prints the current date<\/p>\n<\/td>\n<\/tr>\n
\n
&T<\/p>\n<\/td>\n
\n
Prints the current time<\/p>\n<\/td>\n<\/tr>\n
\n
&F<\/p>\n<\/td>\n
\n
Prints the name of the document<\/p>\n<\/td>\n<\/tr>\n
\n
&A<\/p>\n<\/td>\n
\n
Prints the name of the workbook tab<\/p>\n<\/td>\n<\/tr>\n
\n
&P<\/p>\n<\/td>\n
\n
Prints the page number<\/p>\n<\/td>\n<\/tr>\n
\n
&P+<\/p>\n<\/td>\n
\n
Prints the page number plus the specified number<\/p>\n<\/td>\n<\/tr>\n
\n
&P-<\/p>\n<\/td>\n
\n
Prints the page number minus the specified number<\/p>\n<\/td>\n<\/tr>\n
\n
&&<\/p>\n<\/td>\n
\n
Prints a single ampersand<\/p>\n<\/td>\n<\/tr>\n
\n
&N<\/p>\n<\/td>\n
\n
Prints the total number of pages in the document<\/p>\n<\/td>\n<\/tr>\n
\n
&Z<\/p>\n<\/td>\n
\n
Prints the file path<\/p>\n<\/td>\n<\/tr>\n
\n
&G<\/p>\n<\/td>\n
\n
Inserts an image<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
This is shown here:<\/p>\n
$worksheet.pageSetup.CenterFooter = \"Time: &T\"\n$worksheet.pageSetup.RightFooter = \"Page &P of &N\"\n<\/pre>\n
In Office Excel 2007 you cannot print preview a spreadsheet that does not have any data in it. So we need to at least add a single character in a single cell:<\/p>\n
$worksheet.cells.item(1,1) = \"a\"<\/pre>\n
Because I do not like to mouse around if I do not have to and because the best way to see the fine spreadsheet we have created is in print preview mode, we decided to go ahead and add this to the script:<\/p>\n
$workbook.printPreview()<\/pre>\n
When we run the script, we see this:<\/p>\n
\"Image<\/p>\n
 <\/p>\n
Well that is about all there is to working with Office Excel footers. MN, I hope you enjoyed the article, and find the information useful. See you tomorrow for Quick-Hits Friday.<\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/b><\/span><\/font><\/p>\n
<\/b><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a simple question for you: How can I modify the footers in an Office Excel spreadsheet? – MN Hi MN, I have a simple answer for you as well. You assign a value to the appropriate footer property of the pagesetup object. If you would prefer a longer answer, read […]<\/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,48,49,3,45],"class_list":["post-54603","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a simple question for you: How can I modify the footers in an Office Excel spreadsheet? – MN Hi MN, I have a simple answer for you as well. You assign a value to the appropriate footer property of the pagesetup object. If you would prefer a longer answer, read […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54603","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=54603"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54603\/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=54603"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54603"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54603"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}