Today a simple use case example – Excel to PDF exporter using Excel VBA. PDF is the current standard printing format. Saving an Excel file to PDF is not hard feat, however, sometimes you need to repeat this operation multiple times or even have a whole lot of Excel documents that need printing. Why do it manually when we can harness the power of VBA?

Excel to PDF: Worksheets

Let’s start small. Exporting a single Worksheet to PDF is nothing hard and can be done using the following procedure:

Sub PrintToPDF(arr As Variant, fileName As String, _
    Optional vQuality = xlQualityStandard, _
    Optional vIncDocProperties = True, _
    Optional vIgnorePrintAreas = False, _
    Optional vOpenAferPublish = False)
    
    ThisWorkbook.Sheets(arr).Select
    Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        fileName:=fileName, _
        Quality:=vQuality, _
        IncludeDocProperties:=vIncDocProperties, _
        IgnorePrintAreas:=vIgnorePrintAreas, _
        OpenAfterPublish:=vOpenAferPublish
    ActiveSheet.Select
End Sub

How to run it? See example below:

Sub SingleExport_Click()
    PrintToPDF Array("Sheet1", "Sheet2"), "C:\temp1.pdf"
End Sub

Example of what the code does:
Feel free to modify any of the optional parameters accordingly.

Batch Excel to PDF

Consider the situation – you have a number of Excel files which need to be converted to PDF files. You can open each of them, select the required Worksheets and save them as PDF. Why bother when a simple macro will do the trick?

The BatchExport_Click procedure below will print all Excel files within a selected directory to a subfolder named pdf.

Sub BatchExport_Click()
    Dim fldr As Object, folder As String, fileName As String, outputFolder As String, wb As Workbook, app As Excel.Application
    '----Select folder----
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select folder with Excel files to export to PDF"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo EndSub
        folder = .SelectedItems(1)
    End With
    '----Output directory---
    outputFolder = folder & "\pdf\"
    On Error Resume Next
    MkDir (outputFolder)
    On Error GoTo 0
    Set app = New Excel.Application
    '---Loop and print to pdf---
    fileName = Dir(folder & "\")
    Do Until fileName = vbNullString
        Set wb = app.Workbooks.Open(folder & "\" & fileName)
        PrintWBToPDF wb, outputFolder & GetFileFromPath(fileName)
        wb.Close
        fileName = Dir()
    Loop
    app.Quit
    Set app = Nothing
EndSub:
    MsgBox "Finished!"
End Sub

Sub PrintWBToPDF(wb As Workbook, fileName As String, _
    Optional vQuality = xlQualityStandard, _
    Optional vIncDocProperties = True, _
    Optional vIgnorePrintAreas = False, _
    Optional vOpenAferPublish = False)
    wb.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        fileName:=fileName, _
        Quality:=vQuality, _
        IncludeDocProperties:=vIncDocProperties, _
        IgnorePrintAreas:=vIgnorePrintAreas, _
        OpenAfterPublish:=vOpenAferPublish
End Sub
Function GetFileFromPath(ByVal strPath As String) As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFileFromPath = GetFileFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Batch export: How it works

The macro is really simple and works as follows:

Run the macro

A popup window will appear asking you to select a folder which contains only Excel files which you want to export to PDF:

Select folder with files and proceed

After you select the folder the VBA Macro will create a subdirectory called pdf. This directory will contain all pdf files.

Download Excel to PDF

Want to download the whole code and start exporting your Excel files to PDF format? Feel free to download a working copy below: