Having modified a piece of code to pull all .xml files into excel, I have attempted to modify the code, to pull a specific file from all directories in a filepath. Ideally, I want it to go through all subdirectories from a root path. Though from what I can see, the VBA "DIR" function needs a static path to work from - is there any way to work the DIR command to include all sub-directories from a root-path?
Sub xmlImport()
fld = "\\root-directory\"
i = 1
found = False
Fil = Dir(fld & "*file.xml")
Do While (Fil <> "")
found = False
Location = fld & Fil
ActiveWorkbook.xmlImport URL:=Location, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A" & i)
Do
If Cells(i, 1) = "" Then
found = True
Else
i = i + 1
End If
Loop Until found
Fil = Dir
Loop
End Sub