excel vba not exporting pagesetup to pdf correctly

Question

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

Can I have a look at the code that formats the sheet to the desired layout. Because I have just tried your code by changing a sheet I had to a landscape, etc.. and I was able to save the pdf exactly the way I have changed it. — CaptainABC, Jul 21 '14 at 11:00
@CaptainABC I added the code you requested. Thanks for looking at this! — rohrl77, Jul 21 '14 at 11:04
Thanks for posting the rest of the code. I have added an answer. — CaptainABC, Jul 21 '14 at 11:37

score 5 · Accepted Answer · answered Sep 17 '14 at 09:03

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.

CaptainABC · Answer 2 · 2014-07-21T12:52:48.630

4

I think the problem is that you need to add the .Zoom = False to your 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 have tried this should solve it for you.

Let me know how it goes!

EDIT: Maybe you need:

'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 changed:

.PrintArea = Worksheets(ReportWsName).UsedRange

To

.PrintArea = Worksheets(ReportWsName).UsedRange.Address

edited Jul 21 '14 at 12:52

answered Jul 21 '14 at 11:37

CaptainABC

1,229
6
24
40

I tried it, and now I get both my printpreview and the PDF export on multiple pages. At least they now match :-) ... but alas it is not working yet. – rohrl77 Jul 21 '14 at 11:50
Interesting side note... by placing .zoom = False as the first item before .PrintArea etc. it restores the formating to 1 page. – rohrl77 Jul 21 '14 at 11:52
1

Weird behavior continues... if I step through the code doing the page formating using F8 and then run the rest of it using F5, the result turns out as desired. ?!? But simply running the macro in one go brings back the undesired behavior. – rohrl77 Jul 21 '14 at 11:55
@rohrl77 Okay, just a question: How many pages does the output needs to be. 1 pages wide by 1 pages tall? I have edit my answer. Try now. – CaptainABC Jul 21 '14 at 12:14
I tried adding the FitToPagesTall but it deliveres the same result. – rohrl77 Jul 21 '14 at 12:19
1

@rohrl77 That is strange :( works fine for me. Okay, try to add a sub `Private Sub Run()` then under that place `Call CreateNewReport` and `Call SaveAsPDF`. Now try running the created sub. – CaptainABC Jul 21 '14 at 12:22
I followed your suggestion. By creating a new sub and adding only the necessary info, it is formating the PDF correctly!!! What should I be looking at next to figure out what's going wrong? Obviously the formating is killed somewhere along the line... – rohrl77 Jul 21 '14 at 12:36
@rohrl77 Yup! Something along your code is causing it to not work correctly. But the `PageSetup` & the `SaveAsPDF` codes are just fine cause they run correctly for me. That said, it would be hard for me to further assist without trying it on your actual file. – CaptainABC Jul 21 '14 at 12:45
Agreed. THanks for your help and the time you invested! I'll upvote your help! It's odd though... bcs after the page setup the PDFSave is practically the next thing to happen. But I'll have to look into it further myself. THanks again! – rohrl77 Jul 21 '14 at 12:47
@rohrl77 wait a sec... Check out edit2 on my answer! – CaptainABC Jul 21 '14 at 12:53

score 2 · Answer 3 · answered Nov 18 '16 at 09:35

Yes!!!, I have had the same problem: I was not able to export a sheet with the page Setup settings already applied on it.

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

So just add Application.PrintCommunication=false before your code , on pagesetup settings like CaptainABC says and most important: close with Application.PrintCommunication=true after the code.

Thank you for this useful post.

excel vba not exporting pagesetup to pdf correctly

3 Answers3

Linked