Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    FileSearch Directory Wildcards (VBA)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch Directory Wildcards (VBA)

    Here is a way to find all the folders. You can then use Dir or FileSearchObject to find the files within each folder.
    <code>
    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</code>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch Directory Wildcards (VBA)

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

    "Q:data??156*.tif"

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch Directory Wildcards (VBA)

    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.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FileSearch Directory Wildcards (VBA)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch Directory Wildcards (VBA)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>... how would this go then?
    <pre> 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
    </pre>

    Alan

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch Directory Wildcards (VBA)

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

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FileSearch Directory Wildcards (VBA)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>, 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 <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    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.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FileSearch Directory Wildcards (VBA)

    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.

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FileSearch Directory Wildcards (VBA)

    Thanks, Hans. That's even easier than I remember. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch Directory Wildcards (VBA)

    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 <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    <code>
    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
    </code>


    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •