Odd Powershell behaviour when exporting an Excel-worksheet to a PDF file

Question

I've written a Powershell script that converts every worksheet within an Excel-workbook to a seperate PDF-file. The code below works properly when the output PDF-file fits within one page.

When the content of the worksheet is large and should normally be saved to two pages, it attempts to fit it on one page. This results in a cell overlapping the footer. It should be on the next page.

The error message shows an error in the ExportAsFixedFormat method. This exception only shows when the output PDF should be exported across two or more pages.

Value does not fall within the expected range. (System.ArgumentException)

In an attempt to debug this, I came across the PageSetup property. I wanted to see the amount of pages that it contained so I added Write-Host $Worksheet.PageSetup.Pages to the inner loop.

Oddly, adding this Write-Host solved the issue. The content now spreads itself across multiple pages. Even weirder, while the PDF-files get created correctly, the error message is still there. I have two questions:

How did adding a Write-Host statement fix the export?
How do I get rid of the error message (without just supressing it)?

I've also played around with properties like PageSetup.FitToPagesTall (set to 2) and PageSetup.FitToPagesWide (set to 1), but this messed up the layout of the PDF-file.

$Formats = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false

$ExcelFiles = Get-ChildItem $ExcelDirectory -Filter "*.xlsx" -Recurse

foreach($ExcelFile in $ExcelFiles)
{
    $Workbook = $Excel.Workbooks.Open($ExcelFile.FullName)
    $Workbook.Saved = $true

    foreach($Worksheet in $Workbook.Worksheets)
    {

        if($Worksheet.Name -eq "Voorblad")
        {
            continue
        }

        # Some algorithm to calculate the output file name.
        # Not relevant for this question. Results in the variable $SheetPrettyName

        $OutputPath = "$ExcelDirectory\" + $SheetPrettyName + ".pdf"

        $Worksheet.ExportAsFixedFormat($Formats::xlTypePDF, $OutputPath)
    }

    $Excel.Workbooks.Close()
}

$Excel.Quit()

user_8194516 · Answer

So, getting the error message five times and the export having to split the PDF at five points was not related. The Excel-file has been created through a number of templates, which reside in the workbook as invisible worksheets. Coincidentally, there were five hidden worksheets.

The program attempted to export and save these worksheets. But logically, failed to do so.

I've added a check to my loop which checks if the worksheet is hidden or very hidden. These visibility options are represented by a 1 or 2 respectively (-1 for visible).

if(($Worksheet.Name -eq "Voorblad") -or ($Worksheet.Visible -ne -1))
{
    continue
}

I was also able to remove the Write-Host I added earlier, the script remained functional.

Odd Powershell behaviour when exporting an Excel-worksheet to a PDF file

1 Answers