CODEWITHSUNDEEP
vb.netexcel
Andre Lombaard
Andre Lombaard

Reputation: 7105

Unable to set the PaperSize property of the PageSetup class

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.

Upvotes: 8

Views: 15919

Answers (5)

serge
serge

Reputation: 15239

Your default printer should be installed and available (connected)

  • If you have no default printer installed, you can install a printer driver, or use a PDF generator;
  • if your default printer is installed, but not available (connected): change the default printer to an available one, or use a PDF generator as default one;

Upvotes: -1

Walter Stabosz
Walter Stabosz

Reputation: 7735

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.

Upvotes: 2

Leo G.
Leo G.

Reputation: 2139

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.

Upvotes: 2

Alphapico
Alphapico

Reputation: 3043

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

Upvotes: 7

I kiet
I kiet

Reputation: 174

You can use office automation to change the page size something like this

worksheet.PageSetup.PaperSize = WdPaperSize.wdPaperLetter

or choose any other paper type from the dropdown list on "WdPaperSize."

hope it will help you a bit.

Upvotes: -1

Related Questions