Excel VBA macro to print your file to PDF..

For 2007 and up:

In excel 2007 there is a built-in option: File / Save & Send / Create PDF & XPS Document.

The macro below does that for you, faster and easier. Assign it to a button and change the sheets array and references to ActiveSheet to have a standardized button.

I use this to print 3 sheets to 1 PDF in a specific report.

Sub Save_as_pdf()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

    Set FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ThisWorkbook.FullName
    If FSO.FileExists(s(0)) Then
        '//Change Excel Extension to PDF extension in FilePath
        s(1) = FSO.GetExtensionName(s(0))
        If s(1) <> "" Then
            s(1) = "." & s(1)
            sNewFilePath = "D:\file.pdf"
            '//Export to PDF with new File Path
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sNewFilePath, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
        '//Error: file path not found

        MsgBox "Error: this workbook may be unsaved.  Please save and try again."
    End If
    Set FSO = Nothing

        Row = Row + 1
    Call MsgBox("All PDF files have been created!")

End Sub

For Excel 2003 & older:

100% free solution to printing your excel file to PDF.

First of all you need the CutePDF. This program acts like a printer on your computer.

Now after installing CutePDF, when you run this macro, you will get a pop-up to name your PDF file, and select a location to save it.
So this is not a solution if you are planning to save lots and lots of files, but it worked great for me.

Sub Print_To_PDF()

'Select all visible sheets, except for SOURCE DATA
    For Each ws In Sheets
        If ws.Name <> "SOURCE DATA" And ws.Visible Then ws.Select (False)

'Print to PDF

    Application.ActivePrinter = "CutePDF Writer on CPW2:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub
Posted in VBA Snippets Tagged with: , , , ,
One comment on “Excel VBA macro to print your file to PDF..
  1. Daniel says:

    Thanks for the above it is very useful.

    How would I also automatically put a filename for the sheets and location.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>