Selected Answer
Valentino
Thanks for clarifying your question. I'll give a generic answer but will NOT refer to the file/ URL mentioned in your question- if you have questions on a paid VBA course, please don't post them (or code/ files) here or elsewhere. Instead use the Contact link (in the green bar above) and use the Premium Courses option. Don will then answer you directly but allow him some time- he's a busy man!
In the file attached, there are two procedures in Module1. That starts with some declarations (which define and allow items to be shared between the two procedures):
Public ObjFolder As Object, objSubFile As Object
Public objFso As Object
Public objFldLoop As Object
Public TargName As String
Then the first procedure is where you define what to search for and where (via the bold comments and bits in quotation marks below):
Sub GetStructure()
' enable use of FSO
Set objFso = CreateObject("Scripting.FileSystemObject")
'### define top level folder path
Set ObjFolder = objFso.GetFolder("E:\Documents\Spreadsheets\TeachExcel files\")
' ### define the partial of full name of *.xlsm target file (without extension)
TargName = "letters"
' make sure at least 5 characters in names
If Len(TargName) < 5 Then Exit Sub
' loop through this folder
For Each objSubFile In ObjFolder.Files
' check it's a macro file and matches target name
If Right(LCase(objSubFile.Name), 5) = ".xlsm" _
And InStr(LCase(objSubFile.Name), LCase(TargName)) > 0 Then
' print in Intermediate window
Debug.Print "* Found: " & objSubFile.Name & vbCr & " at " & objSubFile.Path
End If
Next objSubFile
' call the function to loop through its subfolders (and theirs)
LoopEachFolder ObjFolder
' quit FSO
Set objFso = Nothing
End Sub
After running through the top-level folder, that passes that folder name to the function below (which calls itself iteratively):
Function LoopEachFolder(fldFolder As Object)
' With the subfolders in this folder....
For Each objFldLoop In fldFolder.subFolders
' ...loop through files in this folder
For Each objSubFile In objFldLoop.Files
' check it's a macro file and matches target name
If Right(LCase(objSubFile.Name), 5) = ".xlsm" _
And InStr(LCase(objSubFile.Name), LCase(TargName)) > 0 Then
' print in Intermediate window
Debug.Print "* Found: " & objSubFile.Name & vbCr & " at " & objSubFile.Path
End If
Next objSubFile
' ... and run the function on each subfolder found
LoopEachFolder objFldLoop
Next objFldLoop
End Function
It will search every folder and subfolder below the top-level folder you define (but may take some time!).
In my case I was searching for .xlsm file including the string "letters" in my folder E:\Documents\Spreadsheets\TeachExcel files\ (which you might change to C:\ on your user's computer).
When you run the first procedure, it will print one or more results like that below (in VBA's Immediate Window, in which you can copy text):
* Found: Get letters from numbers and select multiple columns v0_a.xlsm
at E:\Documents\Spreadsheets\TeachExcel files\Tutorial files\Get letters from numbers and select multiple columns v0_a.xlsm
You'll need to change "letters" for as much as you know EXACTLY about the user's file name but note I included a line which means it does nothing unless that string had 5 characters or more (otherwise you're likely to get many matches).
Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.
