Set Excel Page Size to A3

[ria.arora]

Hi

I'm creating Excel File Dashboard from MS Access VBA and trying to setup the page size to A3 using below code:

Sub SetUpPage(ws As Worksheet, Heading As String, Orient As String)
   On Error GoTo ErrHand
   Dim myOrient As String
   
   ws.Application.ScreenUpdating = False
   With ws
     With .PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""Bookman Old Style,Bold""&14&K09-024&U" & Heading & " (" & gsReportingWeekNm & ")"
        .RightHeader = "Page &P of &N"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = ws.Application.InchesToPoints(0.25)
        .RightMargin = ws.Application.InchesToPoints(0.25)
        .TopMargin = ws.Application.InchesToPoints(0.5)
        .BottomMargin = ws.Application.InchesToPoints(0.25)
        .HeaderMargin = ws.Application.InchesToPoints(0.25)
        .FooterMargin = ws.Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = Excel.xlPrintNoComments
        .CenterHorizontally = True
        .CenterVertically = False
        If Orient = "P" Then myOrient = Excel.xlPortrait Else If Orient = "L" Then myOrient = Excel.xlLandscape
        .Orientation = myOrient
        .Draft = False
        .PaperSize = Excel.xlPaperA3 '----> Error
        .FirstPageNumber = Excel.xlAutomatic
        .Order = Excel.xlDownThenOver
        .BlackAndWhite = False
     End With
   End With
   ws.Application.ScreenUpdating = True
ExitSub:
  Exit Sub
ErrHand:
  Resume ExitSub
End Sub

While setting up the page size to A4 I'm getting error if printer is not installed. I want to set the page size to A3 whether printer is not installed because Excel Dashboard is generated in Network drive and from there user will print the dashboard.

Any idea how to set the page size to A3 without installing the printer.

 

[ria.arora]

Highly appreciate your help and response on this.

 

[vbaInet]

I don't have a machine without printers installed so I can't test it but will it work if you install a PDF printer? Or you're not allowed to install anything?

 

[ria.arora]

If A3 page size driver is install then this works Even if its dummy printer But at work place I can't install dummy printer in all user's desktop

Ant idea how to tackle this?

 

[vbaInet]

Well that's a dependency on a print driver. Perhaps build your report from a template.

 

[ria.arora]

I can't build the report using template because this has to be dynamic

I'm sure someone must have done this

Any idea???

 

[vbaInet]

I would that if there's a workaround for this one since there's a dependency.

Post your question in the Excel section of the forum to see if someone has some other ideas.

 

[ria.arora]

Ok Will try in excel section as but this needs to be done from access VBA

 

[vbaInet]

The methods you are calling are Excel VBA. The only time Access VBA is used is when you create an instance of Excel, everything else is VBA for Excel.