Save a Microsoft Excel Workbook as a PDF File by Using PowerShell

ExportTo-ExcelPDF.ps1

# ref: https://devblogs.microsoft.com/scripting/save-a-microsoft-excel-workbook-as-a-pdf-file-by-using-powershell/
# run as administrator priviledge

$path = Get-Location 
$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()

Just a hint, maybe someone other has this error too using this script:

If your $filepath is a Powershell remote path like:

PS Microsoft.PowerShell.Core\FileSystem::\\server.lan\share\path\to\directory\export.pdf

the export command will fail with: "Value does not fall within the expected range".
German message is: "Der Wert liegt außerhalb des erwarteten Bereichs".

I changed $filepath to a local, temporary directory and moved the file over to the destination.

Also this script mechanism can be used to export single worksheets instead of the whole workbook. Simply load the desired worksheet into a variable and use ExportAsFixedFormat from there:

$workSheet = $workbook.worksheets("WorksheetName") #select worksheet by name.
$worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)

when i run this code i don't got response, because it's hanging in

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

then i have printed the content of '$xlFixedFormat::xlTypePDF'(using "$xlFixedFormat::xlTypePDF saving $filepath"), which is

::xlTypePDF saving C:\\ex1\a.pdf