To convert multiple word files (.docx) to PDF using VBA Excel.
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.
We had written this VBA code in excel, so here we have allocated this code to command button as shown below.
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.
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.
Once we select input folder, another dialog box will appear, wherein we shall select the output folder.
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.
Once we execute the VBA code, both the word files will be converted to pdf files as shown below.
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