In this article you will learn how to convert multiple Excel files into PDF file using VBA. This is quite useful and easy macro. We have used File System Object method here.
To use the file system object you need to add “Microsoft Scripting Runtime” Reference. To add this Reference go to Visual basic editor > Tools > References > Microsoft Scripting Runtime
We have taken the two folder path on excel worksheet (on Sheet1)
- Excel Folder Path: On cell E13, we have taken Excel Folder Path wherein multiple excel files are available.
- PDF Folder Path: On cell E14, we have taken PDF Folder Path wherein PDF file will be stored after the conversion.
Below is the VBA code to convert Excel to PDF
Sub Excel_To_PDF() Application.ScreenUpdating = False Application.DisplayStatusBar = True Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Sheet1") Dim fso As New FileSystemObject Dim fo As Folder Dim f As File Dim wb As Workbook Dim n As Integer Set fo = fso.GetFolder(sh.Range("E13").Value) For Each f In fo.Files VBA.DoEvents n = n + 1 Application.StatusBar = "Processing..." & n & "/" & fo.Files.Count Set wb = Workbooks.Open(f.Path) wb.ExportAsFixedFormat xlTypePDF, sh.Range("E14").Value & Application.PathSeparator & VBA.Replace(f.Name, ".xlsx", ".pdf") wb.Close False Next Application.StatusBar = "" MsgBox "Process Completed" End Sub
Click here to download this Excel workbook.
Watch the step by step video tutorial: