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?

score 1 · Answer 1 · answered Mar 23 '18 at 10:20

1

For the bottom one don't forget to fully qualify the file name with its extension and consider using Path separator to concatenate. For example:

Sub Find_Files()

    Dim f As String

    f = ThisWorkbook.Path
    ibox = "701000034955.xlsb"
    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) + 1) = Application.Transpose(sn)

End Sub

answered Mar 23 '18 at 10:20

QHarr

83,427
12
54
101

Thanks QHarr. I still get the same error in the last line – Selrac Mar 23 '18 at 12:48
When you have the error, what do you get in the immediate window if you put ?f & Application.PathSeparator & ibox and hit enter? – QHarr Mar 23 '18 at 12:49

score 1 · Accepted Answer · answered Mar 25 '18 at 21:23

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

Find file in sub directory

2 Answers2