Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

excel vba not exporting pagesetup to pdf correctly

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

If I manually export and save it as a pdf, then it uses the correct page setup, producing a one page PDF that is in landscape. However, the same export done by VBA produces a PDF that is severalpages long and in portrait.

i can't figure out why it's doing this. i've tried various solutions such as selecting the worksheet before exporting it, but all to no avail.

Any help is appreciated.

Code looks like this:

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

UPDATE:

Code used to format the pagesetup (since it's rather long I am only adding the relevant section of that sub)

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
like image 706
rohrl77 Avatar asked Jul 21 '14 09:07

rohrl77


1 Answers

I have found what seems to be the 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 whatever reason Excel would overwrite the settings I was putting if I ran the code without it.

like image 175
rohrl77 Avatar answered Sep 19 '22 00:09

rohrl77