Excel vba does not correctly export pages to pdf

Question

I have code that formats a worksheet for the desired setup and layout (one page wide and tall in the landscape). When I run the code (part of a long macro), it correctly formats the page.

If I manually export and save it as a pdf, then it uses the correct page setup, creating a one-page PDF file that is in the landscape. However, the same export done by VBA creates a PDF file that lasts several pages in the portrait as well.

I can’t understand why he is doing this. I tried various solutions, such as selecting a worksheet before exporting it, but all to no avail.

Any help is appreciated.

The code is as follows:

Sub SaveAsPDF() Sheets(ReportWsName).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ [SaveFolderPath] & "\" & ReportWsName, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False End Sub 

UPDATE:

The code used to format the page set (since it is quite long, I only add the corresponding section of this subdirectory)

 Private Sub CreateNewReport(ProvisionCode As String, TimeFrom As Date, TimeTo As Date) ... other code here... 'Format report to create the desired layout With Worksheets(ReportWsName) 'Delete unnecessary data and format the rest .Range("A:B,D:D,F:G,J:M,O:O,Q:S").Delete Shift:=xlToLeft .Range("A:F").EntireColumn.AutoFit .Range("C:C, E:F").ColumnWidth = 30 With .Range("G:G") .ColumnWidth = 100 .WrapText = True End With 'Insert standard formating header form Reporting template .Rows("1:2").Insert wsReportTemplate.Range("1:3").Copy .Range("A1") .Range("A2") = "Notes Report for " & ProvisionCode & " (" & TimeFrom & " - " & TimeTo & ")" 'Insert standard formating footer form Reporting template wsReportTemplate.Range("A6:G7").Copy .Range("A" & .UsedRange.Rows.Count + 2) 'Ensure all data is hard coded .UsedRange.Value = .UsedRange.Value 'Format Print Area to one Page With ActiveSheet.PageSetup .PrintArea = Worksheets(ReportWsName).UsedRange .Orientation = xlLandscape .FitToPagesWide = 1 End With End With End Sub 

Answer 1

I found what seems like a solution:

 Application.PrintCommunication = False With ActiveSheet.PageSetup .Orientation = xlLandscape .Zoom = False '.PrintArea = Worksheets(ReportWsName).UsedRange .FitToPagesWide = 1 '.FitToPagesTall = 1 End With Application.PrintCommunication = True 

I needed to add the Application.PrintCommunication part to the equation. For some reason, Excel would rewrite the settings that I set if I ran the code without it.

Answer 2

I think the problem is that you need to add .Zoom = False to the page setup code:

 'Format Print Area to one Page With ActiveSheet.PageSetup .PrintArea = Worksheets(ReportWsName).UsedRange .Orientation = xlLandscape .FitToPagesWide = 1 .Zoom = False 'I have added this line End With 

From what I tried, this should solve it for you.

Let me know how this happens!

EDIT: Perhaps you need to:

 'Format Print Area to one Page With ActiveSheet.PageSetup .PrintArea = Worksheets(ReportWsName).UsedRange .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 .Zoom = False 'I have added this line End With 

EDIT2: What if you change:

.PrintArea = Worksheets(ReportWsName).UsedRange

For

.PrintArea = Worksheets(ReportWsName).UsedRange.Address

Answer 3

Yes !!!, I had the same problem: I could not export the sheet with the page settings that were already applied on it.

Before trying Application.PrintCommunication, I tested the Wait and Sleep commands without success. Finally, I skipped this problem using the CopyPicture method, adding a chart page and then exporting it to pdf, but the resolution in my PDF file was unsatisfactory and I could not play with the margins.

So, just add Application.PrintCommunication = false in front of your code, in your website settings settings such as CaptainABC, and most importantly: close with Application.PrintCommunication = true after the code.

Thanks for this useful post.