VBA Loop Through PDF Files in a Folder

VBA Loop Through all PDF Files in a Folder in Excel

VBA Loop Through PDF Files in a Folder using built-in Dir() function. In this tutorial we loop through a folder or directory and list all available .PDF files in a folder. Lets learn how to use Dir, Right and Len VBA functions in this tutorial.

Example to find all .PDF files in a directory using Excel VBA

Let us see the example macro to loop through all .PDF files in a folder using Excel VBA. The output is displays in the immediate window. We are using Dir function to access all available files in a folder. Right function is used to check ‘\’ is added or not to file path and PDF is available in file name or not. And finally Len function is used to check file length.

'VBA Loop Through .pdf files in a given Folder
Sub VBAF1_List_All_PDF_Files_Using_Dir()
    
    'Variable Declaration
    Dim sFilePath As String
    Dim sFileName As String
    
    'Specify File Path
    sFilePath = "C:\Test"
    
    'Check for back slash
    If Right(sFilePath, 1) <> "\" Then
        sFilePath = sFilePath & "\"
    End If
        
    sFileName = Dir(sFilePath & "*.pdf")
    
    Do While Len(sFileName) > 0
        If Right(sFileName, 3) = "pdf" Then
            'Display file name in immediate window
            Debug.Print sFileName
            
        End If
        'Set the fileName to the next available file
        sFileName = Dir
    Loop
   
End Sub

Output: Here is the output screenshot of above macro.

VBA Loop Through PDF Files in a Folder Example
VBA Loop Through PDF Files in a Folder Example

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment