Find Subfolder file path

[Anmar]

I need code that searches through all sub folders in the C drive and when it it matches to a specified file it returns the file path. Any suggestions?

 

[RobertSF]

VBA has a few native file statements and functions.

The Dir/Dir$ function lets you determine if a file exists. If the file exists, it returns the file name. For example,

'Dir returns a Variant and Dir$ returns a string
Dim varDir as Variant
Dim strDir as String
'Assuming C:\readme.txt exists
varDir = Dir("C:\readme.txt")
'varDir equals "C:\readme.txt")
strDir = Dir$("C:\readme.txt")
'strDir equals "C:\readme.txt"

You can also check if directories exist.
strDir = Dir$("C:\Program Files", vbDirectory)

But searching the whole drive with Dir will be slow, so look into the File System Object. Here's more information on it.
https://www.exceltrick.com/formulas_macros/filesystemobject-in-vba/
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/filesystemobject-object

And don't forget to add a reference to the necessary library, as explained here.
https://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba

This should be enough to get you going. If you later have specific questions, drop by!

 

[Anmar]

I need code that searches through all sub folders in the C drive and when it it matches to a specified file it returns the file path. Any suggestions?

I am using the following code


Sub ChkFolder()


Dim fso, oFolder, oSubfolder, oFile, queue As Collection


Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection
queue.Add fso.GetFolder("C:")


Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue

For Each oSubfolder In oFolder.SubFolders
queue.Add oSubfolder 'enqueue
If oSubfolder.Name = "file name I am looking for" Then
MsgBox ("File Found")
End If
Next oSubfolder


Loop
End Sub

I get the following error while following this code
Run-time erorr '70':
Permission Denied

Is there a way to bypass folders that I do not have permissions for?

 

[RobertSF]

Yes, you can bypass them. Use error handling. In VBA, this consists of the On Error statement. With error handling, your program won't crash.

The first six or seven links are all error handling tutorials, so take your pick.
https://www.google.com/search?q=vba+error+handling