VBA to search sub-directories for specific file

[pricer]

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

 

[pricer]

OK, i've now got code to work that will list out all sub-directories available:

Option Explicit
Dim myRow As Long
Dim wks As Worksheet
Sub list_folders()
Dim folders As String
folders = Sheets("Folders").Range("E2") 'This is where my root folder is specified
Set wks = Sheets("Folders")
myRow = 0
Call FoldersInFolder(folders)
End Sub
Sub FoldersInFolder(myFolderName As String)

'Dim FSO As Scripting.FileSystemObject
'Dim myBaseFolder As Scripting.Folder
'Dim myFolder As Scripting.Folder
'Set FSO = New Scripting.FileSystemObject

Dim FSO As Object
Dim myBaseFolder As Object
Dim myFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")

Set myBaseFolder = FSO.GetFolder(myFolderName)

For Each myFolder In myBaseFolder.SubFolders
myRow = myRow + 1
wks.Cells(myRow, "B").Value = myFolder.Path
Call FoldersInFolder(myFolder.Path)
Next myFolder

End Sub

However, there are over 5000 folders, so the process is quite taxing - as the file I need is always in a sub-folder called "\baseconfig" is there anyway I could modify the above to only pull subdirectories with or end in "\baseconfig".

Thanks

 

[pricer]

OK, i've got a bit further here - my logic being as i know the last subdirectory name, I'll run a RIGHT query on the path string inside an IF statement - however, despite everything matching up (confirmed via msgbox testing) - it's not registering the last RIGHT command as matching, despite it being the correct text and length:

Sub FoldersInFolder(myFolderName As String)
Application.ScreenUpdating = False

Dim FSO As Object
Dim myBaseFolder As Object
Dim myFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")

Set myBaseFolder = FSO.GetFolder(myFolderName)

For Each myFolder In myBaseFolder.SubFolders
MsgBox (Right(myFolder, 10)) '<<<It states "BaseConfig" here
If myFolder = Right(myFolder, 10) = "BaseConfig" Then MsgBox "True" Else MsgBox "False" '<<<<but false here?
myRow = myRow + 1
wks.Cells(myRow, "B").Value = myFolder.Path
Call FoldersInFolder(myFolder.Path)
Next myFolder
Application.ScreenUpdating = True
End Sub

Do I need to cast the Object as a text string in order for the RIGHT query to recognise the path as text? If so, how?

 

[John_w]

MsgBox (Right(myFolder, 10)) '<< If myFolder = Right(myFolder, 10) = "BaseConfig" Then MsgBox "True" Else MsgBox "False" '<<< myRow = myRow + 1

The commented-out If statement is wrong. Try:

If Right(myFolder, 10) = "BaseConfig" Then MsgBox "True" Else MsgBox "False" '<<< myRow = myRow + 1

The process is slow because FSO has to look through every subfolder from your root folder to find the matching subfolders. I don't think there are any FSO shortcuts to do this.

A much faster alternative is to run a DOS DIR command and pipe the output to the DOS FIND command.

 

[brianr278]

I also am using the SFO method to look at several network drives and directories. It is very timne consuming, although managable as I only need to do it once a day.

Regarding the Dir() method, could you provide any specifics about how to look at a directory and all it's subdirectories as well? I too am finding the single directory a limitation. I have looked at the HELP file, but it is not clear to me how to recify this problem.

 

[John_w]

Regarding the Dir() method, could you provide any specifics about how to look at a directory and all it's subdirectories as well? I too am finding the single directory a limitation. I have looked at the HELP file, but it is not clear to me how to recify this problem.

I mentioned the DOS DIR command, which is different to the VBA Dir function. The Dir function is likely to be similar in speed to FSO methods for traversing a directory tree. For a procedure based on the Dir function which looks in folders and subfolders, see http://www.ammara.com/access_image_faq/recursive_folder_search.html