Converting multiple sheet Excel file to PDF using VBA error

Question

I am currently trying to convert a multiple sheet excel file to a PDF using VBA using the following code:

Private Sub CommandButton1_Click()

Dim mySheets As Variant, sh

mySheets = Array("Sheet1", "Sheet2", "Sheet3")
For Each sh In mySheets
    Sheets(sh).PageSetup.Orientation = xlLandscape
Next

Sheets(mySheets).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="S:\GasInc\Services\B&ITS\OpsEng\EngServ\_Station Design\Projects\Station Co-ops\Angela Lian" & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

End Sub

It converts my file fine, however for what I have on sheet 2 it splits it up into multiple pages in the PDF because I guess it does not scale it to fit the page. I was wondering how I could modify the code to make it scale this sheet so it will fit on one page of the PDF.

Thanks!

See this answer. You'll basically have to check the first member of .VPageBreaks, calculate the width to the first break, and then scale to fit the page break. — Comintern
– Comintern, Commented Sep 22, 2016 at 23:48

EricIndecisive · Accepted Answer · 2016-09-23 03:02:48Z

0

I have a similar sub in one of the tools I use. Do you have your worksheet's width / height scale to fit properties set to 1 page?

For Each sh In mySheets
    With Sheets(sh).PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
Next sh

One of the weird things I've run into is scaling issues when converting to PDF with some chart / image objects. For instance a logo looks fine on screen but prints stretched on the pdf. I never fixed the problem, but through research it seemed due to the way the printer sees it compared to the screen resolution. Have you ever run into that problem?

answered Sep 23, 2016 at 3:02

EricIndecisive

264 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting multiple sheet Excel file to PDF using VBA error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related