Hey, Scripting Guy! How Can I Modify the Footers of an Office Excel Spreadsheet?

ScriptingGuy1

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 on.

Today, we have a script called CreateExcelFooter.ps1 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:

$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()
$worksheet = $workbook.worksheets.item(1)
$worksheet.pageSetup.LeftFooter = "Date: &D"
$worksheet.pageSetup.CenterFooter = "Time: &T"
$worksheet.pageSetup.RightFooter = "Page &P of &N"
$worksheet.cells.item(1,1) = "a"
$workbook.printPreview()

The first thing we need to do is to create an instance of the Excel Application object. We do this by using the New-Object cmdlet and specifying the comobject parameter. We store the application object that is returned in the $excel variable. This line of code is seen here:

$excel = new-object -comobject excel.application

Next we want to be able to see our Office Excel application, so we make the visible 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:

$excel.visible = $true

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 property of the application object to return a workbooks collection, and we use the add method. We use the $workbook variable to hold the workbook object that is created:

$workbook = $excel.workbooks.add()

We now use the worksheets property of the workbook object to retrieve the first worksheet. We use the item method of the worksheets object to return the first worksheet. This is seen here:

$worksheet = $workbook.worksheets.item(1)

When we have the worksheet object, we can use the pagesetup property to return the pagesetup object, and then we can access the leftfooter property. To change the left footer, you just assign an acceptable value to the property. We use &D, which is a formatting code that tells Office Excel to add the date (this is documented here):

$worksheet.pageSetup.LeftFooter = "Date: &D"

We can do the same thing to the center footer and to the right footer. For the center footer, we use the &T formatting code to display the current time. For the right footer, we use the &P code to display the current page number, and the &N 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:

Table 1 Excel format codes used for headers and footers

This is shown here:

$worksheet.pageSetup.CenterFooter = "Time: &T"
$worksheet.pageSetup.RightFooter = "Page &P of &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:

$worksheet.cells.item(1,1) = "a"

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:

$workbook.printPreview()

When we run the script, we see this:

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.

Ed Wilson and Craig Liebendorfer, Scripting Guys

ScriptingGuy1

Follow