Home>Blogs>VBA>Automation: Excel to PDF Converter
Excel to PDF Converter
VBA Templates

Automation: Excel to PDF Converter

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.

You can see our other automations:

Excel to PDF Converter

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

Add Reference
Add Reference

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

 

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step by step video tutorial:

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com

Leave a Reply