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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top