Exporting Word Documents to PDF in a Directory with Excel VBA

This function allows you to search for multiple files within a directory that match a certain pattern.

Using this function, the following example exports all Word files with the .docx extension from the subdirectory Export into PDF files:

Code example:

Sub ExportWordFilesToPDFInFolder()
    Dim appWord As Word.Application
    Dim document As Word.Document
    Dim folderPath As String
    Dim wordFileName As String
    Dim pdfFileName As String
    Dim nameParts() As String
    Set appWord = CreateObject("Word.Application")
    folderPath = ThisWorkbook.Path & "\Export"
    ' Get the first .docx file in the folder
    wordFileName = Dir(folderPath & "\*.docx")
    Do While wordFileName <> ""
        ' Open the Word document
        Set document = appWord.Documents.Add(folderPath & "\" & wordFileName)
        ' Split filename into name and extension
        nameParts = Split(wordFileName, ".")
        ' Build PDF filename with same base name but .pdf extension
        pdfFileName = folderPath & "\" & nameParts(0) & ".pdf"
        ' Export the document as PDF
        document.ExportAsFixedFormat _
            OutputFileName:=pdfFileName, _
            ExportFormat:=wdExportFormatPDF
        ' Close document without saving changes
        document.Close SaveChanges:=wdDoNotSaveChanges
        ' Get the next .docx file matching the pattern
        wordFileName = Dir
    Loop
    ' Quit Word application and clean up
    appWord.Quit
    Set document = Nothing
    Set appWord = Nothing
End Sub

Explanation:

• The variable wordFileName initially stores the name of the first .docx file found in the Export folder under the directory of the Excel workbook running this macro.
• If a matching file is found, the Do While loop begins.
• Calling Dir() again without parameters at the end of the loop fetches the next .docx file that matches the pattern.
• The loop ends when no more matching files are found (Dir() returns an empty string).
• Inside the loop, the full filename is split into name and extension parts using the Split() function.
• The PDF filename is then created by replacing the .docx extension with .pdf.
• Each Word document is opened, exported to PDF, and closed without saving changes.