• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1031
  • Last Modified:

Exporting a Range in Excel 2010 to PDF Format

Hi, How do I fit a range into a pdf page ( presumably it is sized to A4 / portrait ).

The code as follows:

Sub TestExportAsFixedFormat()
  Dim rng As Range
  Set rng = Range("A1:L100")
  Dim fileName As String
  fileName = "C:\Test\Export.pdf"
  rng.ExportAsFixedFormat Type:=xlTypePDF, _
   fileName:=fileName, Quality:=xlQualityStandard, _
   IncludeDocProperties:=True, IgnorePrintAreas:=True, _
   From:=1, To:=1, OpenAfterPublish:=True
End Sub

Thank you.
1 Solution
Harry LeeCommented:
The following code should work for you.

Sub TestExportAsFixedFormat()
    Dim rng As Range, OrgPntArea As String, NewPntArea As String, fileName As String
    Set rng = Range("A1:L" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
    fileName = "C:\Test\Export.pdf"

    OrgPntArea = ActiveSheet.PageSetup.PrintArea
    NewPntArea = Range("A1:L" & ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row).Address
    ActiveSheet.PageSetup.PrintArea = NewPntArea

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlPortrait
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
        fileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

    ActiveSheet.PageSetup.PrintArea = OrgPntArea

End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now