FileSearch Directory Wildcards (VBA)

Topic

New Reply

Is there an easy way to do a file search and have a wildcard as one of the subdirectories?

I have a disk structure like
Q:dataAA123
Q:dataAA126
Q:dataBB156
Q:dataCC275

I know the three digit directory, but I do not know the two letter directory name.
I would like to do Dir(“Q:data*156*.tif”) and have it return the tifs in Q:dataBB156

Dir hates * in the directory string.
Application.FileSearch doesn’t crash like Dir, but doesn’t accept the wildcard for .LookIn

Is there anything that works? TIA –Sam

Reply | Quote

Replies

Here is a way to find all the folders. You can then use Dir or FileSearchObject to find the files within each folder.

Dim objWMIService As Object
Dim colFolders As Variant
Dim objFolder As Variant
Set objWMIService = GetObject("winmgmts:" & _
"{impersonationLevel=impersonate}!.rootcimv2")

Set colFolders = objWMIService.ExecQuery _
("SELECT * FROM Win32_Directory WHERE Name Like 'Q:data%156'")

For Each objFolder In colFolders
Debug.Print "Name: " & objFolder.Name
Next objFolder

Reply | Quote

Just a stab in the dark, but how does DIR respond to:

“Q:data??156*.tif”

Alan

Reply | Quote

The Dir function allows wildcards in the file name, but not in the path. Your example would raise runtime error 52: Bad file name or number.

Reply | Quote

That is very cool, but it took forever (5-10 minutes) on a small test system with XL whining about having to wait for another OLE process. Being a glutton for punishment, I ran it again and it only took 20 seconds, However, I ran it again several hours later and it took 6 minutes. This will be running on a terabyte disk server, so even the 20 seconds is not acceptable, I can just manually traverse the directory tree. I haven’t tried using the Windows API directly yet, probably should do that, but it ‘s a pain. Thanks! –Sam

Reply | Quote

… how would this go then?

    Dim MyFile, MyPath, MyName, MyTarget, MyTif
    Dim MyDirs As Collection
	
    ' Display and capture the names in Q:data that represent 2-letter directories.
    MyPath = "Q:data"    ' Set the path.
    MyName = Dir(MyPath, vbDirectory)    ' Retrieve the first entry.
    Do While MyName  ""    ' Start the loop.
        ' Ignore the current directory and the encompassing directory.
        If MyName  "." And MyName  ".." And Len(MyName) = 2 Then
            ' Use bitwise comparison to make sure MyName is a directory.
            If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
                Debug.Print MyName    ' Display and add entry only if it
                MyDirs.Add (MyName)   ' it represents a directory of type ??.
            End If
            
        End If
        MyName = Dir    ' Get next entry.
    Loop
    
    ' Print *.tif files in 156 subdirs of each captured 2-letter directory
    For Each Dir2 In MyDirs
        MyTarget = "Q:data" & Dir2 & "156"
        MyTif = Dir(MyTarget & "*.tif")
        Do While MyTif  ""
            Debug.Print MyTarget & MyTif
            MyTif = Dir
        Loop
        
    Next Dir2

Alan

Reply | Quote

That looks good. Let’s see what SammyB says.

Reply | Quote

, that’s exactly what’s needed. Initially, I thought why bother with Dir, I’ll just use Application.FileSearch
Bad idea, I forgot that I have over a million files, 1400 folders and half a terabyte of tiffs. Not a good environment for FileSearch
I was thinking I would have to construct a sheet with a lookup table, but Alan’s scheme works a treat and and seems fast enough. Thanks for the effort: I hadn’t realized that you need to check the attributes. It’s annoying that the dir(… vbDirectory) includes directories rather than excluding all but directories. Anyway, thanks again! –Sam

PS hijacking the thread. Can you find a link to the ShellAndWait api? I need to process the dozen or so tiffs that I found, wait, and then process the next set. The program that processes the tiffs must have a memory leak because the time to process a tiff increases a minute for every 13 tiffs, so I have to split up my efforts.

Reply | Quote

ShellAndWait is not an API function, it’s a VB/VBA function that uses various API functions to run a process and wait until it’s finished. See for example HowTo: Start another program using Shell and wait until it finishes.

Reply | Quote

Thanks, Hans. That’s even easier than I remember. –Sam

Reply | Quote

A bit more playing about on a warm, lazy Sunday. This function (or a suitable variation) might prove useful. I’ve not tested this “final” form, so hope there are no errors

Function SubDirs(BasePath As String, Pattern As String, Optional rexp As Boolean = False) As String

' Returns a semicolon-delimited string of subdirectories matching the Pattern parameter.
' Pattern matching is done via the Like operator.
' Regular expression pattern match not yet implemented.
'
' Sample usage:
' Dim SubFolders() As String
' SubFolders = Split(SubDirs("C:MainNext", "B?[mM]*"), ";")
'
' will result in SubFolders array containing the strings:
' B1Mabc
' Brm
' BZM0
' if they exist as immediate child subfolders under C:MainNext

Dim myName As String

' Dim myRE As RegExp
'
' If rexp = True Then
' Set myRE = New RegExp
' myRE.Pattern = Pattern
' myRE.Global = False
' myRE.IgnoreCase = True
' myRE.MultiLine = False
' End If

rexp = False ' Not implemented
myName = Dir(BasePath, vbDirectory) ' Attempt to retrieve the first entry.
If LenB(myName) = 0 Then GoTo NoMatch

Do While LenB(myName) > 0 ' Start the loop.
' Ignore the current directory and the encompassing directory.
If myName = "." Or myName = ".." Or Not myName Like Pattern Then GoTo NextLoop

' myName matches the pattern, but still need to
' use bitwise comparison to make sure MyName is a directory.
If (GetAttr(MyPath & myName) And vbDirectory) = vbDirectory Then
Debug.Print myName ' Display and add entry only if it
SubDirs = SubDirs & ";" & myName ' it represents a directory.
End If

End If
myName = Dir ' Get next entry.
NextLoop:
Loop
GoTo Finish

NoMatch:
MsgBox "No matching subdirectories found", vbExclamation

Finish:
' Set myRE = Nothing

End Function


Alan

Reply | Quote