Filespec for searching files and directories

[Airtech]

I am using the AllenBrowne code of "filldirlisttotable" to provide some functions for a media library manager I am building in access 2003.
I have four checkboxes which if all four are not turned on, give a list of extensions that should be search for. However, when I do it that way, I get no results.

The code works great. Only one issue, I cannot find anywhere on the net any notes about using the filespec option.
By default it uses *.*, and I can make it work by specifying only one extension, such as *.jpg, but it becomes useless when I specify multiple extensions such as *.jpg, *.gif, *.png.

Obviously that must be the wrong way to provide the filespec.
Has anyone used this function? I am providing part of it below.
There may even be a better way of doing this, but I have written substantial code around this so I thought I would see if nybody was familiar with it before I started working on a different solution.

I am kind of thinking that unless this code simply cannot handle the multiple extensions regardless of how they are provided, then I may have to re-write this section of code.

Call FillDirToTable(colDirList, strpath, strFileSpec, bIncludeSubfolders)

This goes to:

Private Function FillDirToTable(colDirList As Collection _
, ByVal strFolder As String _
, strFileSpec As String _
, bIncludeSubfolders As Boolean)
 
'Build up a list of files, and then add add to this list, any additional folders
On Error GoTo Err_Handler
 
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String
 
'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount
strSQL = "INSERT INTO zzLibScan " _
& " (MediaFileName, MediaPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop
 
If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If
 
Exit_Handler:
 
Exit Function
 
Err_Handler:
strSQL = "INSERT INTO zzLibScan " _
& " (MediaFileName, MediaPath) " _
& " SELECT "" ~~~ ERROR ~~~""" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL
 
Resume Exit_Handler
End Function
 
Public Function TrailingSlash(varIn As Variant) As String
If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If
End Function

Any help or suggestions are appreciated.

[nico5038]

This code will only allow one filter for the file name extension.
I would advise to call the routine for each extension. The table will "collect" the different extensions for you.

Nic;o)

[ADezii]

I imagine that you would want to Delete the contents of the Table zzLibScan before populating it with your FileSpec results, if so:

Dim varRetVal As Variant
Dim colDirList As New Collection
 
DoCmd.Hourglass True
 
With DoCmd
  .SetWarnings False
     DoCmd.RunSQL "Delete * From zzLibScan"
  .SetWarnings True
End With
 
varRetVal = FillDirToTable(colDirList, "C:\Some Folder", "*.FileSpec", True)
 
DoCmd.Hourglass False

P.S. - If you ran multiple FileSpecs through this Recursive Procedure, I imagine that you would run into potential problems.

[Airtech]

What I am trying to decide is how to feed the multiple file specifications to the function.

On one hand, I can build a list of extensions and parse them out into an array and loop through the array. This benefits users of the program in they would directly choose the file types.

Alternatively, I could ignore the filespec, and use the procedure as written and cappture all files, then discard (which I already have a routine for) the files that the user didn't want. The latter is probably more time consuming.

There has to be a better way of doing this. If there a function, or routine that will do what this routine is doing (just file name to one field and the complete path to another field) being populated in a temporary table with multiple file specifications, I use the two fields for all the other functions, against the individual files, such as getting tags information from MP3s, basic file information, and such in routines afterward.

I suppose the better question is there is better function than this one? Where can I find info, or if there is an open source function, where can I locate?

Thanks for the replies.

CJ

[nico5038]

I would probably create a table with file extensions and a YesNo field that can be set by the user.
In a record set processing loop you can activate the function for all extensions having the YesNo field set to True.
(And ofcourse empty the table first with ADezii's DELETE)

Nic;o)

[Airtech]

That is not a bad idea, then I can pass the needed extensions through a loop based on the on/off field for the extenion. That would also make it VERY easy to add more extensions as I develop the software.

Nico, AWESOME suggestion, I had not thought of that.
Extension definition table it is, then loop through the valid record for the filldirtotable function.

THANKS!

CJ

[ADezii]

I actually had my doubts about how effective the code would be with multiple File Specs, so I ran it with 4 different File Specifications, and here are the results following the posted code:

Dim varRetVal As Variant
Dim colDirList As New Collection
Dim intCounter As Integer
 
DoCmd.Hourglass True
 
With DoCmd
  .SetWarnings False
     DoCmd.RunSQL "Delete * From zzLibScan"
  .SetWarnings True
End With
 
For intCounter = 1 To 4
  Select Case intCounter
    Case 1
      varRetVal = FillDirToTable(colDirList, "C:\", "*.exe", True)
    Case 2
      varRetVal = FillDirToTable(colDirList, "C:\", "*.dll", True)
    Case 3
      varRetVal = FillDirToTable(colDirList, "C:\", "*.bmp", True)
    Case 4
      varRetVal = FillDirToTable(colDirList, "C:\", "*.txt", True)
    Case Else
  End Select
Next
 
DoCmd.Hourglass False

OUTPUT:
Files returned matching the 4 Specs: 4,826
Records containing Errors: 60

[nico5038]

Glad you see the advantage :-)

The code needed could use a query selecting the true extensions like:

 
function fncFillFiles()
 
dim rs as DAO.Recordset
 
set rs = currentdb.openrecordset("select extension from tblExtension where SelectYN = True")
 
if rs.eof and rs.bof then
   msgbox "No selected extensions"
   exit function
endif
 
' initialize target table when needed
currentdb.execute ("delete * from tblResults")
 
while not rs.EOF
   ' change to use the proper colDirList and strpath
   Call FillDirToTable(colDirList, strpath, rs!FileSpec, bIncludeSubfolders)
   rs.movenext
wend
 
end function
 

Nic;o)

[ADezii]

@nico5038
Nice approach, Nico. Any speculation why the 60 Errors (1.24% Error Rate)?

[nico5038]

Guess the errors are SQL errors on the INSERT.
In such a case I suspect a field being inserted is empty while defined to be required and/or a unique indexed field that's causing duplicate entries.

Can you check the files causing this trouble? Perhaps they are having a hidden or other property set causing the dir() statement to fail. Another option could be the (sub) folders that are returning . or .. as "filename".

Nic;o)

[ADezii]

@nico5038
Here's an interesting one for you. I ran the same code at home on one of my stand-alone PCs, and came up with the following results:

1,083 - *.exe
3,785 - *.dll
  693 - *.bmp
  556 - *.txt
6,117 Files Returned

P.S. - Not a single Error.

[nico5038]

Hmm, than it is probably something in the regional settings causing this.

Nic;o)

[ADezii]

@nico5038

1. Here's another one that will make your head spin. Running the following code on my Home PC will Append 3,785 Files to the Table zzLibScan, meaning 3,785 Files will be returned for the C:\*.dll FileSpec, including Sub-Directories:
   

   Dim varRetVal As Variant
   Dim colDirList As New Collection
    
   DoCmd.Hourglass True
    
   With DoCmd
     .SetWarnings False
        DoCmd.RunSQL "Delete * From zzLibScan"
     .SetWarnings True
   End With
    
   varRetVal = FillDirToTable(colDirList, "C:\", "*.dll", True)
    
   DoCmd.Hourglass False
    

2. Executing the following Command in the Root Directory of Drive C: from the Command Prompt will display 6,928 Files matching the same FileSpec.
   

   'From the Root Directory of Drive C:
   C:\> Dir *.dll /s

3. But...
   

   varRetVal = FillDirToTable(colDirList, "C:\Windows", "*.dll", False)

   and
   

   C:\Windows> Dir *.dll

   will produce exactly the same number of Files matching the specification. Weird, or is it just me?

[nico5038]

That's just you :-)

What's the difference in file names ? I guess the function does "group" the files by name (.dll's are notoriously occurring everywhere) while the command gives them all.

Does the temp table hold an index ?

Nic;o)

[ADezii]

@nico5038
Just ignore me, I guess I'm still hung over from New Years. I'll figure it out eventually! (LOL).