Powershell code to export Excel to PDF in lanscape or fit2page mode.

[darkgen]

I'm mucking about with a little code which basically recurses through a folder and exports a worksheet from a workbook to PDF. The issue I'm having to trying to include reference to either page setup/orientation or print area so the PDF created is either set to landscape or fit to page mode.

$path = ""
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]
$filter = Get-ChildItem -Path $path -include *.xlsx -recurse
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false
foreach($wb in $filter)
{
    #$filepath = ($wb.FullName -replace '.xlsx?$', '.xps') #xps
    $filepath = ($wb.FullName -replace '.xlsx?$', '.pdf')
    $workbook = $objExcel.workbooks.open($wb.fullname, 3)
    $workbook.Saved = $true
    $Worksheets = $Workbook.worksheets
    $Worksheet = $Workbook.worksheets.Item(1)
    #$Workbook.PageSetup.Orientation = landscape #Like this worked! :)
    #$Worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypeXPS, $filepath) #xps
    $Worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
    $objExcel.Workbooks.close()
}
$objExcel.Quit()

Would appreciate any pointers!
Thanks, Paul.

 

[d_brennen]

This any good?

$excel.ActiveWorkbook.ActiveSheet.PageSetup.Orientation = 2

or perhaps

$excel.Worksheets.Item("Sheet1").PageSetup.Orientation = 2

 

[darkgen]

Property 'Orientation' not found on the first line. The second via

    $Worksheet = $Workbook.worksheets.Item(1)
    $workbook.Worksheets.Item("Sheet1").PageSetup.Orientation = 2

Does not error but makes no difference to the PDF generated.

Hmmm.

EDIT - Actually it did. I rather stupidly did not change the worksheet name to reflect the actual worksheet name in the workbook.

Many thanks; out of interest do you think a setting exists for Page2fit?

 

[d_brennen]

I don't think so, sorry

 

[darkgen]

Fair enough; Bit of a blag work around but by changing the page layout scaling on each worksheet provides a means to mimic Page2fit.

 

[EvanDonovan]

Here is code that should work. It does for me, once the workbook is opened:

# for each of the worksheets in the workbook create a PDF with the same name as the worksheet
foreach($worksheet in $workbook.Worksheets) {
    $filepath = "C:\EXAMPLE\" + $worksheet.Name + ".pdf"
    # change to landscape orientation
    $worksheet.PageSetup.Orientation = 2
    # make zoom = 100 so it doesn't scale down
    $worksheet.PageSetup.Zoom = 100
    # see https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.exportasfixedformat
    $worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
    write-Output "Created file: $filepath"
}