VBA Loop Through all XML Files

VBA Loop Through all XML Files in a Folder

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

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

Let us see the example macro to loop through all .XML files in a folder using Excel VBA. The output is displayed 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 XML is available in file name or not. And finally Len function is used to check file length.

'VBA Loop Through .XML files in a given Folder
Sub VBAF1_List_All_XML_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 & "*.XML")
    
    Do While Len(sFileName) > 0
        If Right(sFileName, 3) = "XML" 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 all XML Files Example
VBA Loop Through all XML Files Example

Instructions to use Macro

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output in the immediate window
  • you can find above specified input and output screenshot for your reference and better understand.

Related Articles

You can also learn complete details like syntax, example and etc. about Dir, Right and Len functions.

VBA Dir Function VBA Len Function VBA Right Function

Leave a Comment

Your email address will not be published. Required fields are marked *