Unable to set the PaperSize property of the PageSetup class

Question

I'm trying to print an excel spreadsheet using VB.NET but I'm getting an error

Unable to set the PaperSize property of the PageSetup class

Here is my code,

Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
    System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

    With application
        .AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable
        .Visible = False
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    Dim workbook As Excel.Workbook
    Dim worksheet As Excel.Worksheet
    'Open as readonly and do not update links
    workbook = application.Workbooks.Open(_fileName, 2, True)

    For Each worksheet In workbook.Worksheets
        worksheet.PageSetup.PaperSize = _paperSize
    Next

    workbook.PrintOutEx()
    workbook.Close(False)
    application.Quit()

    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

    worksheet = Nothing
    application = Nothing

This code works on my development machine, as soon as I deploy to the test server the code fails. There is already a default printer driver installed on the server.

Answer 1

You need to install printer drivers. The PageSetup class of Excel must interact with the printer drivers

I'm having similar issue running related code on Windows 8 operating under Mac Parallels (Virtual Machine). For anyone who are doing Visual Studio deployment on Mac Parallels, here's what I'm doing:

1. Disable Printers sharing between Mac and Windows. Go to Parallels (VM) > Configure > Hardware and click Print. Uncheck 'Add all Mac printers' and 'Synchronize Default Printer'
2. Install printer's driver on Windows
3. In order to get assigned IP address by the same wireless router (if you need to connect to printer wirelessly), you need to set up networking as bridged. Go to Parallels (VM) > Configure > Hardware > 'Network 1' and choose Networking type as Airport and DHCP server as Auto

Answer 2

The issue at the core is in the _paperSize constant. In my case, it was giving the same error on: (forgive c#)

worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLedger; 

depending on the printer, for example the "ledger" size paper may be defined as 11x17 or Tabloid. Find out what the print driver refers to the page sizing and then

DIDN'T WORK:

excel.ActiveSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLedger 

excel.ActiveSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaper11x17

WORKED:

excel.ActiveSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperTabloid

I hope this can help anyone having frustrating issues with funny inconsistencies of Microsoft Excel's enums.