CODEWITHSUNDEEP
excelautomation
masfenix
masfenix

Reputation: 7996

saving excel files as a pdf

I am looking for the best solution to do the following:

I have around 640 Excel files each with about 9 - 12 sheets in them. I would like to save each sheet as a PDF. Currently, the process is done manually. Select sheet -> File -> Save as -> pdf -> navigate through a million folders. As you can see this is very time consuming and inefficient.

I know how to program in C# however I don't have visual studio on my machine. However if the best way to do this is VS then maybe I can get the IT team to install VS if they have a license for me.

The only tools available to me are Excel (not even Access is available). So I will be mainly looking for VBA code. If this means opening up each file to paste VBA code in, it will still be quicker then saving each sheet as PDF.

Upvotes: 0

Views: 5508

Answers (1)

Eric J.
Eric J.

Reputation: 150108

You can accomplish your goal using PowerShell

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()

http://blogs.technet.com/b/heyscriptingguy/archive/2010/09/06/save-a-microsoft-excel-workbook-as-a-pdf-file-by-using-powershell.aspx

It is also fairly straightforward to use the same COM API in C#, which you can accomplish with the free Visual Studio 2012 Express.

Upvotes: 2

Related Questions