Change Orientation to xlLandscape without first print preview

Question

I have code that includes changing some of the .PageSetup variables.

I'm having issues where I use this code

Sub Test()

    Dim ws as Worksheet
    
    Set ws = ActiveSheet
    
    With ws.PageSetup
        .Orientation = xlLandscape
    End With

End Sub

This will error out, but if I pause the code before running .Orientation and go to Print Preview, then try running it, everything works fine.

It seems the .PageSetup features need to have the printer settings loaded before being able to execute. I don't know how to do this with VBA.

I am running this from Access DB VBA. It exports a table that I then create a new Excel Application to open it with using

Dim xlApp as Excel.Application
set xlApp = New Excel.Application

Historically, as long as I have the Excel Reference Library loaded, any VBA I write here works the same in Access as it would in Excel.

But just to compare, I've also had this issue when inside of an Excel file running the VBA, its just not consistent, so I've never figured out what the issue could be. But any time I do have the problem, the solution is always just select Print Preview first.

This is the error code I get on my specific macro:

Error Number: 1004

Error Description: Unable to set the Orientation property of the PageSetup class

The macro does a ton of queries and table updates before it gets to the Excel export. Also, the error message seems to be fairly generic with the 1004 number.

Answer 1

You can try to replace xlLandscape by value 2. Take a look at xlPageOrientation enumeration (Excel)

Answer 2

Try turning off Application.PrintCommunication before you work with page setup and then turn it back on afterwards. Like this:

Application.PrintCommunication = False

With ws.PageSetup
    .Orientation = xlLandscape
End With

Application.PrintCommunication = True

That might just move the error on to the line where you turn print communication back on, though

Answer 3

If I remember it correctly, the worksheet (print area) has to be visible otherwise you will get that error. After you open the workbook exported from Access, make sure: workbooks(x).windows(1).visible = True and workbooks(x).sheets("xxx").Visible = xlSheetVisible

I would avoid the use of "Active...".

Also the command: Application.PrintCommunication = False has nothing to do with your error, but without it, the changes you intended to make to the PageSetup will not work.