Exporting All Excel Files in a Directory to PDF in Excel VBA

This section exports all Excel files with the .xlsx extension from the Export subdirectory into PDF files.

Code example:

Sub ExportExcelFilesToPDFInFolder()
    Dim appExcel As Excel.Application
    Dim workbook As Excel.Workbook
    Dim folderPath As String
    Dim excelFileName As String
    Dim pdfFileName As String
    Dim nameParts() As String
    Set appExcel = CreateObject("Excel.Application")
    folderPath = ThisWorkbook.Path & "\Export"
    ' Get the first .xlsx file in the folder
    excelFileName = Dir(folderPath & "\*.xlsx")
    Do While excelFileName <> ""
        ' Open the Excel workbook
        Set workbook = appExcel.Workbooks.Add(folderPath & "\" & excelFileName)
        ' Split filename into name and extension
        nameParts = Split(excelFileName, ".")
        ' Build PDF filename with same base name but .pdf extension
        pdfFileName = folderPath & "\" & nameParts(0) & ".pdf"
        ' Export the workbook as PDF
        workbook.ExportAsFixedFormat _
            Type:=xlTypePDF, Filename:=pdfFileName
        ' Close workbook without saving changes
        workbook.Close SaveChanges:=False
        ' Get the next .xlsx file matching the pattern
        excelFileName = Dir
    Loop
    ' Quit Excel application and clean up
    appExcel.Quit
    Set workbook = Nothing
    Set appExcel = Nothing
End Sub

Explanation:

• The process uses the Dir() function, a Do While loop, and the Split() function.
• The Dir() function is first called with the search pattern *.xlsx to get the first Excel file.
• The loop opens each Excel file in the folder, splits its filename to separate the base name and extension, and builds the output PDF filename by replacing the extension with .pdf.
• Each workbook is then exported as a PDF using ExportAsFixedFormat.
• After exporting, the workbook is closed without saving any changes.
• The loop continues by calling Dir() without parameters to fetch subsequent .xlsx files until none remain.
• Finally, the Excel application is quit and object references are cleaned up.