VBA to search sub-directories for specific file

pricer

New Member
Joined
Mar 21, 2007
Messages
25
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

pricer

New Member
Joined
Mar 21, 2007
Messages
25
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
 
Last edited:

pricer

New Member
Joined
Mar 21, 2007
Messages
25
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,410
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:
Code:
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

New Member
Joined
Nov 28, 2008
Messages
1
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.:confused:
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,410
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.:confused:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top