CODEWITHSUNDEEP
vbaexcelpdf
Eduardo Rocha
Eduardo Rocha

Reputation: 103

How to set fixed margins on Excel VBA code (PDF is printing in 2 pages instead of 1)

I have an Excel file which has a button "Generate PDF" that runs a macro to print a certain sheet (lets call it "QUOTE") into a PDF. This sheet is shown to be well limited in margins and in my computer the created PDF has the perfect structure: everything is well included in 1 page. However, in some other computers, when the PDF is created, everything does not fit into 1 page and a 2nd page is created with a bit of content. Here is the code (including the attempts to fix this problem by limiting the margins):

Sub Excel_Export_Proposal()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsCOTIZACION As Worksheet
Dim Proposalname As String
Dim iVis As XlSheetVisibility
Dim xlName As Excel.Name
Dim FolderPath As String
Dim myRange As String

Set wsQUOTE = ThisWorkbook.Sheets("QUOTE")

FolderPath = ActiveWorkbook.Path & "\"


Proposalname = "Quote for " & CStr(Range("B2").Value)

wsQUOTE.PageSetup.PrintArea = myRange
With wsQUOTE.PageSetup
.FitToPagesTall = 1
.FitToPagesWide = False
.Zoom = False
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.75)


End With

'Proposal
Application.ScreenUpdating = False
wb.Unprotect
With wsQUOTE
iVis = .Visible
.Visible = xlSheetVisible
.ExportAsFixedFormat Type:=xlTypePDF, _
                     Filename:=ActiveWorkbook.Path & "\" & Proposalname & ".pdf", _
                     Quality:=xlQualityStandard, _
                     IncludeDocProperties:=True, _
                     IgnorePrintAreas:=True, _
                     OpenAfterPublish:=True


.Visible = iVis

wsQUOTE.Activate


End With



wb.Protect 
Application.ScreenUpdating = True
End Sub

Can somebody help me fixing this problem? I would like that the sheet would we printed perfectly regardless of the computer or software in which is generated...

Upvotes: 3

Views: 12450

Answers (1)

EEM
EEM

Reputation: 6659

In order to have the procedure Excel_Export_Proposal including the PrintingArea in one page always the following adjustments should be applied:

  1. Set the printing area correctly:
    This line sets the printing area: wsQUOTE.PageSetup.PrintArea = myRange
    However no value is assigned to the variable myRange before this line, therefore the PrintArea is set to "" which is equivalent to setting it to the entire UsedRange of the wsQUOTE sheet.

  2. To ensure that the entire PrintArea is printed in one page the FitToPagesTall and FitToPagesWide must be set to 1
    Replace .FitToPagesWide = False with .FitToPagesWide = 1
    And remove .Zoom = False as it has not effect after setting FitToPagesTall and FitToPagesWide to 1

  3. To ensure that the ExportAsFixedFormat method uses the print areas as defined in the target excel file set the IgnorePrintAreas parameter to False.
    Replace this line IgnorePrintAreas:=True, _ with this line IgnorePrintAreas:=False, _

Below is the revised procedure:

    Sub Excel_Export_Proposal_Revised()
    Dim wb As Workbook, wsQuote As Worksheet
    Dim myRange As String, Proposalname As String, FolderPath As String
    Dim iVis As XlSheetVisibility

        Set wb = ThisWorkbook
        Set wsQuote = wb.Sheets("QUOTE")
        FolderPath = wb.Path & "\"
        Proposalname = "Quote for " & wsQuote.Range("B2").Value2

        'Update myRange with the address of the range to be printed
        myRange = "$B$2:$O$58"  'Change as required

        Application.ScreenUpdating = False

        With wsQuote.PageSetup
            .PrintArea = myRange
            .FitToPagesTall = 1
            .FitToPagesWide = 1     'Set FitToPagesWide to 1
            .LeftMargin = Application.InchesToPoints(0.7)
            .RightMargin = Application.InchesToPoints(0.4)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
        End With

        'Proposal
        wb.Unprotect
        With wsQuote
            iVis = .Visible
            .Visible = xlSheetVisible
            .Activate
            .ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=FolderPath & Proposalname & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True
            .Visible = iVis
        End With
        wb.Protect

        Application.ScreenUpdating = True

        End Sub

See following pages for additional information on the resources used:

Worksheet.ExportAsFixedFormat Method (Excel)
PageSetup Object (Excel)

Upvotes: 6

Related Questions