Exporting Files in a Directory Hierarchy in Excel VBA

This section describes how to export all Word documents (.docx files) and Excel workbooks (.xlsx files) from the subdirectory Export and all its subdirectories into PDF files. The resulting PDFs will be saved in the same directories as their source files.

Key Concepts:

• All entries (files and folders) within a directory are traversed using the Dir() function.
• When an entry is a Word document, it is exported to PDF using Word’s ExportAsFixedFormat() method.
• When an entry is an Excel workbook, it is exported to PDF using Excel’s ExportAsFixedFormat() method.
• If the entry is a subdirectory, its name is added to a Collection object for later processing.
• After completing the traversal of the current directory, the procedure recursively processes each stored subdirectory in the collection.

Important Note on Dir() and Recursion:

Dir() maintains internal state regarding the current search pattern and directory. It cannot handle multiple concurrent search contexts, which makes recursive calls that rely on Dir() within a directory traversal problematic.

To work around this, the recursive calls for subdirectories are performed after finishing the current directory traversal, iterating through the stored collection of subdirectories.

Entry Procedure: HierarchieStart()

This procedure initializes the Word and Excel application objects, sets the starting path, and calls the recursive procedure to begin traversal.

Sub HierarchieStart()
    Dim path As String
    Dim appWord As Word.Application
    Dim appExcel As Excel.Application
    path = ThisWorkbook.Path & "\Export"
    Set appWord = CreateObject("Word.Application")
    Set appExcel = CreateObject("Excel.Application")
    HierarchieUnter path, appWord, appExcel
    appWord.Quit
    appExcel.Quit
    Set appWord = Nothing
    Set appExcel = Nothing
    MsgBox "Finished"
End Sub

Recursive Procedure: HierarchieUnter()

This procedure traverses a given directory, exports all Word and Excel files it finds, and collects subdirectories to recurse into afterward.

Sub HierarchySub(path As String, _
                 appWord As Word.Application, _
                 appExcel As Excel.Application)
    Dim directoryList As New Collection
    Dim directory As Variant
    Dim entry As String
    Dim fullEntry As String
    Dim nameParts() As String
    Dim length As Integer
    Dim document As Word.Document
    Dim workbook As Excel.Workbook
    Dim pdfFileName As String
    entry = Dir(path & "\*", vbDirectory) ' Get first entry including directories
    Do While entry <> ""
        fullEntry = path & "\" & entry
        nameParts = Split(entry, ".")
        length = UBound(nameParts) - LBound(nameParts) + 1
        ' If file with exactly two parts and extension .docx or .xlsx
        If length = 2 Then
            If nameParts(1) = "docx" Then
                Set document = appWord.Documents.Add(fullEntry)
                pdfFileName = path & "\" & nameParts(0) & ".pdf"
                document.ExportAsFixedFormat _
                    OutputFileName:=pdfFileName, _
                    ExportFormat:=wdExportFormatPDF
                document.Close SaveChanges:=wdDoNotSaveChanges
                Set document = Nothing
            ElseIf nameParts(1) = "xlsx" Then
                Set workbook = appExcel.Workbooks.Add(fullEntry)
                pdfFileName = path & "\" & nameParts(0) & ".pdf"
                workbook.ExportAsFixedFormat _
                    Type:=xlTypePDF, Filename:=pdfFileName
                workbook.Close SaveChanges:=False
                Set workbook = Nothing
            End If
        End If
        ' Ignore the current and parent directory entries "." and ".."
        If entry <> "." And entry <> ".." Then
            ' Check if entry is a directory
            If (GetAttr(fullEntry) And vbDirectory) > 0 Then
                directoryList.Add fullEntry
            End If
        End If
        entry = Dir() ' Get next entry
    Loop
    ' Recurse into collected subdirectories
    For Each directory In directoryList
        HierarchySub directory, appWord, appExcel
    Next directory
End Sub

Explanation:

• The initial call to Dir() with the attribute vbDirectory retrieves all entries (files and folders).
• Each entry’s full path is checked.
• Using Split(), the filename is split into name and extension parts.
• Files with extensions .docx or .xlsx are exported accordingly to PDF.
• Entries representing current . and parent .. directories are skipped.
• For all other entries, the attribute is checked to determine if it is a directory; if yes, it is added to a Collection.
• After completing the directory traversal, the procedure recursively calls itself for each subdirectory stored in the Collection.

This approach efficiently walks through all folders and subfolders exporting Word and Excel files to PDFs, keeping each PDF in the corresponding directory.