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.

 

[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?

 

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