Find file in sub directory

Question

I need to do a code in VBA to find a file in a subdirectory.

With the code from 'brettdj' in this link I can find the file if I specify the full directory

Sub LoopThroughFiles()
    Dim MyObj As Object, MySource As Object, file As Variant
    file = Dir("\\A\B\C\D\")
    While (file <> "")
        If InStr(file, "701000034955") > 0 Then
            MsgBox "found " & file
            Exit Sub
        End If
    file = Dir
  Wend
End Sub

I'm looking for a why to not to have to specify the full directory.

I tried the code in this link, but I get a 'type mistmatch' error message in the last line

Sub Find_Files()       
    f = "\\A\B\"        
    ibox = "701000034955"        
    sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & f & ibox & """ /s /a /b").stdout.readall, vbCrLf)         
    Sheets("Sheet1").[A1].Resize(UBound(sn) + 1) = Application.Transpose(sn) ' I get an error message in this line    
End Sub

Any ideas on why the code above is not working and if there is a better solution to search in subfolders for a file?

Answer 1

your second code differs from the first one in that this latter searches for any file in given folder (and subfolders) whose name is exactly "701000034955" while the former searches for file whose name contains that string

hence I guess you just have to use some wildchars

ibox = "*701000034955*" 
sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & f & Application.PathSeparator & ibox & """ /s /a /b").stdout.readall, vbCrLf)
Sheets("Sheet1").[A1].Resize(UBound(sn)) = Application.Transpose(sn)

note the resizing is UBound(sn) instead of UBound(sn) + 1 since there's one endingvbCrlf generating an empty entry in the last position of sn