Convert or Save As multiple word (.docx) files to PDF in VBA Excel

convert word file to pdf vba excel

Objective

To convert multiple word files (.docx) to PDF using VBA Excel.

Approach

Here we had 2 folders, one was input folder which contained multiple .docx files, while there was another output folder which was blank initially. So, we created a macro file (.xlsm) and created a command button on that macro file, which is shown in the image below. This command button is allocated the macro code which is shown below.

Now, when we click on this command button, first a message box will appear which will ask to select an input folder. Then a dialog box will appear, here we shall select the input folder which contains multiple word documents. Once we select the input box and click “ok”, another message box will show up, it will ask us to select output folder. Then second dialog box will show up, then we shall select the output folder.

Once these steps are taken all the output files will be created in the output folder we had selected. This code is completely dynamic, so we are not dependent upon any particular path or folder name.

First, we need to set the library reference as shown in the image below.We need to add “Microsoft Word 16.0 Object Library” as a reference.

 

convert word file to pdf vba excel

 

We had written this VBA code in excel, so here we have allocated this code to command button as shown below.

 

convert word file to pdf vba excel

 

In the below image we can see that we had an input and output folder, however  we can give these folders any name as we want.We shall select these folders using dialog box. Input folder contains word files.

 

convert word file to pdf vba excel

 

When we click on the command button as shown in the image button, a dialog box will show up, then we shall select the input file folder, we can see that in the image below.

 

convert word file to pdf vba excel

 

Once we select input folder, another dialog box will appear, wherein we shall select the output folder.

 

convert word file to pdf vba excel

 

In the below image we can see that we had 2 sample .docx files which we shall convert to pdf. There can be as many files as we need, but for demonstration purpose we have used only these 2 files.

 

convert word file to pdf vba excel

Once we execute the VBA code, both the word files will be converted to pdf files as shown below.

 

convert word file to pdf vba excel

 

Code

Sub ConvertMultipleWordToPDF()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim OpenSourceFolder As Object, OpenTargetFolder As Object
Dim SelectedWordFilesFolder As String, SelectedPdfFilesFolder As String
Dim InputWordFile As String, OutputPdfFile As String


Dim objWordApp As Word.Application
Dim objMyWordFile As Word.Document
Set objWordApp = CreateObject("Word.Application")


Set OpenSourceFolder = Application.FileDialog(msoFileDialogFolderPicker)
Set OpenTargetFolder = Application.FileDialog(msoFileDialogFolderPicker)

'Select input file folder
MsgBox ("Select input folder where word files are stored")
Set OpenSourceFolder = Application.FileDialog(msoFileDialogFolderPicker)

If OpenSourceFolder.Show = -1 Then
SelectedWordFilesFolder = OpenSourceFolder.SelectedItems(1)
End If


If SelectedWordFilesFolder = "" Then
MsgBox "No input folder selected, code will exit"
Exit Sub
End If


AppActivate Application.Caption

'Select output file folder
MsgBox ("Select output folder where PDF files are stored")
If OpenTargetFolder.Show = -1 Then
SelectedPdfFilesFolder = OpenTargetFolder.SelectedItems(1)
End If


If SelectedPdfFilesFolder = "" Then
MsgBox "No output folder selected, code will exit"
Exit Sub
End If



'Looping through only word files in input file folder
InputWordFile = Dir(SelectedWordFilesFolder & "\*.docx")
While InputWordFile <> ""
          
Set objMyWordFile = objWordApp.Documents.Open(SelectedWordFilesFolder & "\" & InputWordFile)
objWordApp.Visible = True
OutputPdfFile = SelectedPdfFilesFolder & "\" & Replace(objMyWordFile.Name, "docx", "pdf")
objWordApp.ActiveDocument.ExportAsFixedFormat OutputFileName:=OutputPdfFile, ExportFormat:=wdExportFormatPDF
      
objMyWordFile.Close
InputWordFile = Dir
Wend

objWordApp.Documents.Application.Quit

End Sub

 

We can run this code for multiple word files, this code is independent of folder names.It will only convert .docx files to pdf files, while it will ignore other types of file in input folder, if they exist.

Post you may like

Convert multiple xls files to xlsx using VBA Excel

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.application.filedialog
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.exportasfixedformat