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.

Solution 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

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

Solution 3

Confirmation of XSham's answer from Microsoft:

https://support.microsoft.com/de-de/help/291298/you-cannot-use-page-setup-properties-in-excel-if-no-printers-were-inst

Symptoms

When you run a Microsoft Visual Basic for Applications macro that attempts to set or get the page setup properties for any sheet in a workbook in Microsoft Excel, you may receive either of the following error messages:

Run-time error '1004': Unable to set the x property of the PageSetup class Run-time error '1004': Unable to get the x property of the PageSetup class

Cause

This problem occurs when there are no printer drivers installed on your computer. Excel cannot set or get page setup properties if no printer drivers are installed.

Resolution

To prevent this problem from occurring, install a printer driver on your computer. Use the Printers option in Control Panel to add and remove printer drivers.