Summary: Microsoft Scripting Guys show you how to use Windows PowerShell to save a Microsoft Excel workbook as a PDF file.

 Hey, Scripting Guy! There are some things I really enjoy about working for a small company. I am the only IT person, and as a result I get a chance to do everything. I work with the Exchange Server. I add users to Active Directory. I handle the backup software, perform database maintenance, update antivirus signature patterns, approve updates for the SUS server, and handle all help desk calls. Actually, the help desk is my office phone. And when I am not busy, I even conduct user training sessions on new products that I deploy such as Office 2010.

I usually go home at the end of the day tired, but satisfied. There is only one weed on the putting green of my otherwise idyllic golf course of life: Every single Friday, I have to go into the accounting share and convert about a dozen Microsoft Excel spreadsheets to PDF files so that our account can email them to our auditors. I have tried and tried and tried to train this otherwise extremely intelligent bean counter how to perform this task himself, but for whatever, he refuses to do it. Because I work for him, I have no recourse (don’t ask me why the “IT department” reports to the “Accounting department”; some things are beyond me.) To make matters worse, this guy seems to have nothing else to do on Friday nights than to call me at home and force me to come to the office and save his Excel files as PDF files.

If you could pull off some sort of scripting magic, it would make my life significantly better and perhaps save me from the same fate that befell the accountant at work. There are no other jobs in the small town where I live, and with teenagers in school, moving is completely out of the question. Help!

— DF

 Hello DF,

Microsoft Scripting Guy Ed Wilson here. As amazing as it sounds, learning to use Windows PowerShell can in fact make a difference in your life—sometimes a big difference. I was in Denver, Colorado, a few years ago teaching a Windows PowerShell class, and right before the class was to start on Monday morning, a person poked his head in the door, came walking on in, grabbed my hand, and began pumping it with great vigor. He gushed about how learning Windows PowerShell had given his life back to him. Before the class, he was beset with a thousand little tasks that required manual attention. As a result, he was working 10 to 12 hours per day, 6 or 7 days a week. His wife was frazzled, his kids were clamoring for attention, and he was about to the breaking point. He was not too keen when his boss suggested he spend a week in class to learn Windows PowerShell. But after he did, he went back to work and automated everything he could think of to automate. Within weeks, he had seen a noticeable improvement in the reliability of his network. Within months, he was down to working 40 hours a week and at times even taking Friday afternoons off to spend with his family. I was visibly touched. The class was somewhat incredulous, but by midweek, they also began to get it.

DF, this script is not a life changer, at least not for me. If it solves a problem for you and addresses a major pain point, I am ecstatic. The complete ExportTo-ExcelPDF.ps1 script is shown here. The only thing you will need to change is the path to the Microsoft Excel files.

ExportTo-ExcelPDF.ps1

$path = “c:\fso”
$xlFixedFormat = “Microsoft.Office.Interop.Excel.xlFixedFormatType” -as [type]
$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false
foreach($wb in $excelFiles)
{
 $filepath = Join-Path -Path $path -ChildPath ($wb.BaseName + “.pdf”)
 $workbook = $objExcel.workbooks.open($wb.fullname, 3)
 $workbook.Saved = $true
“saving $filepath”
 $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
 $objExcel.Workbooks.close()
}
$objExcel.Quit()

Unfortunately, Microsoft Word uses the SaveAs method to save a Word document as a PDF file. Microsoft Excel uses a completely different method, the ExportAsFixedFormat method. It is not too bad after you figure out what you need to do. As a matter of a fact, the ExportAsFixedFormat method is much easier to use than the Microsoft Word SaveAs method, which requires passing values by reference.

The ExportAsFixedFormat method needs to be told how to export the workbook, which can be exported as either a PDF file or a Microsoft XPS file. The cool thing about a Microsoft XPS file is that it does not require installing third-party software, so any updates the XPS viewer requires come on patch Tuesday with all the other updates. Therefore, DF, you could use your Windows Server Update Services server to schedule updates for it.

The XPS file is a Microsoft “stealth product,” a really cool application that we never seem to tell people about. In fact, I did not even know about it until a couple of months ago when one of my tweeps (a mutual follower on Twitter) asked me about it. The way you tell the ExportAsFixedFormat method how to save the file is by using an xlFixedFormatType enumeration value. I did a whole series about enums on Weekend Scripter a while back. You may wish to refer to those articles for help in working with enums. To create the xlFixedFormatType enumeration, use the code shown here:

$xlFixedFormat = “Microsoft.Office.Interop.Excel.xlFixedFormatType” -as [type]

The Get-ChildItem cmdlet is used to collect all the Microsoft Excel files in the folder specified by the $path variable:

$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse

To work with Excel, you first need to create the Excel.Application object. I set the visible property to false to avoid a bunch of spreadsheets popping up and closing. This section of the code is shown here:

$objExcel = New-Object -ComObject excel.application

$objExcel.visible = $false

I use the foreach statement to loop through the collection of files. Inside the loop, I first create a path to the newly created PDF file. I then open the workbook with a value of 3 which means to update links when opening. I then set the saved property to true, and display a status message. This is shown here:

foreach($wb in $excelFiles)

{

 $filepath = Join-Path -Path $path -ChildPath ($wb.BaseName + “.pdf”)

 $workbook = $objExcel.workbooks.open($wb.fullname, 3)

 $workbook.Saved = $true

“saving $filepath”

The only “unique” code in the entire script is the call to ExportAsFixedFormat. This method is overloaded with only one mandatory parameter, the type parameter. I also prefer to specify the filename and path for the export operation. No object is returned from the ExportAsFixedFormat method. After the method has been called, the workbook is closed and the script loops to the next file in the collection. When all the files have been processed, the quit method of the Excel.Application object is called. This is shown here:

$workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)

 $objExcel.Workbooks.close()

}

$objExcel.Quit()

When the script has run, the output shown in the following image appears in the Windows PowerShell ISE.

The folder specified in the path now contains the newly created PDF files. This is shown in the following image.

If you want to create XPS files, you need to change only the file name and type in the script. The modified script is shown here.

ExportTo-ExcelXPS.ps1

$path = “c:\fso”

$xlFixedFormat = “Microsoft.Office.Interop.Excel.xlFixedFormatType” -as [type]

$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse

$objExcel = New-Object -ComObject excel.application

$objExcel.visible = $false

foreach($wb in $excelFiles)

{

 $filepath = Join-Path -Path $path -ChildPath ($wb.BaseName + “.xps”)

 $workbook = $objExcel.workbooks.open($wb.fullname, 3)

 $workbook.Saved = $true

“saving $filepath”

 $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypeXPS, $filepath)

 $objExcel.Workbooks.close()

}

$objExcel.Quit()

All of the Microsoft Excel workbooks are saved as XPS files when the ExportTo-ExcelXPS.ps1 script runs. One of the files is shown in the Microsoft XPS viewer in the following image.

DF, that is all there is to using Windows PowerShell to save Microsoft Excel workbooks as PDF files. As an added bonus, I also wrote a script to save workbooks as XPS files. Microsoft Excel Week will continue tomorrow when we will talk about removing document information from a Microsoft Excel workbook.

We invite you to follow us on Twitter and Facebook. If you have any questions, send email to us at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson and Craig Liebendorfer, Scripting Guys