In the previous article you have learnt how to Excel to PDF converter using VBA. In this article you will learn how to convert multiple Word 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
Word to PDF Converter
To use the Word application you need to add “Microsoft Word 15.0 Object Library” Reference. You may get some different number is place of 15.0 in MS Word reference name. It is depend on the version of Microsoft Office. We are using Microsoft Office 2013.
To add this Reference go to Visual basic editor > Tools > References > Microsoft Word 15.0 Object Library
We have taken the two path on excel worksheet (on Sheet1)
- Word 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 stored after the conversion.
Below is the VBA code to convert Excel to PDF
Option Explicit Sub Word_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 Dim wordapp As New Word.Application Dim worddoc As Word.Document Set fo = fso.GetFolder(sh.Range("E13").Value) For Each f In fo.Files n = n + 1 Application.StatusBar = "Processing..." & n & "/" & fo.Files.Count Set worddoc = wordapp.Documents.Open(f.Path) worddoc.ExportAsFixedFormat sh.Range("E14").Value & Application.PathSeparator & VBA.Replace(f.Name, ".docx", ".pdf"), wdExportFormatPDF worddoc.Close False Next Application.StatusBar = "" MsgBox "Process Completed" End Sub
Visit our YouTube channel to learn step-by-step video tutorials
