Filespec for searching files and directories

[Airtech]

I am using the AllenBrowne code of "filldirlisttot able" 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)