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
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
Select folder with files and proceed
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:
Be sure to follow / subscribe to my posts!