By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

Filespec for searching files and directories

P: 14
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.
Expand|Select|Wrap|Line Numbers
  1. Call FillDirToTable(colDirList, strpath, strFileSpec, bIncludeSubfolders)
This goes to:
Expand|Select|Wrap|Line Numbers
  1. Private Function FillDirToTable(colDirList As Collection _
  2. , ByVal strFolder As String _
  3. , strFileSpec As String _
  4. , bIncludeSubfolders As Boolean)
  5.  
  6. 'Build up a list of files, and then add add to this list, any additional folders
  7. On Error GoTo Err_Handler
  8.  
  9. Dim strTemp As String
  10. Dim colFolders As New Collection
  11. Dim vFolderName As Variant
  12. Dim strSQL As String
  13.  
  14. 'Add the files to the folder.
  15. strFolder = TrailingSlash(strFolder)
  16. strTemp = Dir(strFolder & strFileSpec)
  17. Do While strTemp <> vbNullString
  18. gCount = gCount + 1
  19. SysCmd acSysCmdSetStatus, gCount
  20. strSQL = "INSERT INTO zzLibScan " _
  21. & " (MediaFileName, MediaPath) " _
  22. & " SELECT """ & strTemp & """" _
  23. & ", """ & strFolder & """;"
  24. CurrentDb.Execute strSQL
  25. colDirList.Add strFolder & strTemp
  26. strTemp = Dir
  27. Loop
  28.  
  29. If bIncludeSubfolders Then
  30. 'Build collection of additional subfolders.
  31. strTemp = Dir(strFolder, vbDirectory)
  32. Do While strTemp <> vbNullString
  33. If (strTemp <> ".") And (strTemp <> "..") Then
  34. If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
  35. colFolders.Add strTemp
  36. End If
  37. End If
  38. strTemp = Dir
  39. Loop
  40. 'Call function recursively for each subfolder.
  41. For Each vFolderName In colFolders
  42. Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
  43. Next vFolderName
  44. End If
  45.  
  46. Exit_Handler:
  47.  
  48. Exit Function
  49.  
  50. Err_Handler:
  51. strSQL = "INSERT INTO zzLibScan " _
  52. & " (MediaFileName, MediaPath) " _
  53. & " SELECT "" ~~~ ERROR ~~~""" _
  54. & ", """ & strFolder & """;"
  55. CurrentDb.Execute strSQL
  56.  
  57. Resume Exit_Handler
  58. End Function
  59.  
  60. Public Function TrailingSlash(varIn As Variant) As String
  61. If Len(varIn) > 0& Then
  62. If Right(varIn, 1&) = "\" Then
  63. TrailingSlash = varIn
  64. Else
  65. TrailingSlash = varIn & "\"
  66. End If
  67. End If
  68. End Function
Any help or suggestions are appreciated.
Jan 2 '09 #1
Share this Question
Share on Google+
14 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 2 '09 #2

ADezii
Expert 5K+
P: 8,597
I imagine that you would want to Delete the contents of the Table zzLibScan before populating it with your FileSpec results, if so:
Expand|Select|Wrap|Line Numbers
  1. Dim varRetVal As Variant
  2. Dim colDirList As New Collection
  3.  
  4. DoCmd.Hourglass True
  5.  
  6. With DoCmd
  7.   .SetWarnings False
  8.      DoCmd.RunSQL "Delete * From zzLibScan"
  9.   .SetWarnings True
  10. End With
  11.  
  12. varRetVal = FillDirToTable(colDirList, "C:\Some Folder", "*.FileSpec", True)
  13.  
  14. DoCmd.Hourglass False
P.S. - If you ran multiple FileSpecs through this Recursive Procedure, I imagine that you would run into potential problems.
Jan 3 '09 #3

P: 14
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
Jan 3 '09 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 3 '09 #5

P: 14
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
Jan 3 '09 #6

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. Dim varRetVal As Variant
  2. Dim colDirList As New Collection
  3. Dim intCounter As Integer
  4.  
  5. DoCmd.Hourglass True
  6.  
  7. With DoCmd
  8.   .SetWarnings False
  9.      DoCmd.RunSQL "Delete * From zzLibScan"
  10.   .SetWarnings True
  11. End With
  12.  
  13. For intCounter = 1 To 4
  14.   Select Case intCounter
  15.     Case 1
  16.       varRetVal = FillDirToTable(colDirList, "C:\", "*.exe", True)
  17.     Case 2
  18.       varRetVal = FillDirToTable(colDirList, "C:\", "*.dll", True)
  19.     Case 3
  20.       varRetVal = FillDirToTable(colDirList, "C:\", "*.bmp", True)
  21.     Case 4
  22.       varRetVal = FillDirToTable(colDirList, "C:\", "*.txt", True)
  23.     Case Else
  24.   End Select
  25. Next
  26.  
  27. DoCmd.Hourglass False
OUTPUT:
Files returned matching the 4 Specs: 4,826
Records containing Errors: 60
Jan 3 '09 #7

nico5038
Expert 2.5K+
P: 3,072
Glad you see the advantage :-)

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. function fncFillFiles()
  3.  
  4. dim rs as DAO.Recordset
  5.  
  6. set rs = currentdb.openrecordset("select extension from tblExtension where SelectYN = True")
  7.  
  8. if rs.eof and rs.bof then
  9.    msgbox "No selected extensions"
  10.    exit function
  11. endif
  12.  
  13. ' initialize target table when needed
  14. currentdb.execute ("delete * from tblResults")
  15.  
  16. while not rs.EOF
  17.    ' change to use the proper colDirList and strpath
  18.    Call FillDirToTable(colDirList, strpath, rs!FileSpec, bIncludeSubfolders)
  19.    rs.movenext
  20. wend
  21.  
  22. end function
  23.  
Nic;o)
Jan 3 '09 #8

ADezii
Expert 5K+
P: 8,597
@nico5038
Nice approach, Nico. Any speculation why the 60 Errors (1.24% Error Rate)?
Jan 3 '09 #9

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 4 '09 #10

ADezii
Expert 5K+
P: 8,597
@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:
Expand|Select|Wrap|Line Numbers
  1. 1,083 - *.exe
  2. 3,785 - *.dll
  3.   693 - *.bmp
  4.   556 - *.txt
  5. 6,117 Files Returned
P.S. - Not a single Error.
Jan 4 '09 #11

nico5038
Expert 2.5K+
P: 3,072
Hmm, than it is probably something in the regional settings causing this.

Nic;o)
Jan 4 '09 #12

ADezii
Expert 5K+
P: 8,597
@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:
    Expand|Select|Wrap|Line Numbers
    1. Dim varRetVal As Variant
    2. Dim colDirList As New Collection
    3.  
    4. DoCmd.Hourglass True
    5.  
    6. With DoCmd
    7.   .SetWarnings False
    8.      DoCmd.RunSQL "Delete * From zzLibScan"
    9.   .SetWarnings True
    10. End With
    11.  
    12. varRetVal = FillDirToTable(colDirList, "C:\", "*.dll", True)
    13.  
    14. DoCmd.Hourglass False
    15.  
  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.
    Expand|Select|Wrap|Line Numbers
    1. 'From the Root Directory of Drive C:
    2. C:\> Dir *.dll /s
  3. But...
    Expand|Select|Wrap|Line Numbers
    1. varRetVal = FillDirToTable(colDirList, "C:\Windows", "*.dll", False)
    and
    Expand|Select|Wrap|Line Numbers
    1. C:\Windows> Dir *.dll
    will produce exactly the same number of Files matching the specification. Weird, or is it just me?
Jan 5 '09 #13

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 5 '09 #14

ADezii
Expert 5K+
P: 8,597
@nico5038
Just ignore me, I guess I'm still hung over from New Years. I'll figure it out eventually! (LOL).
Jan 5 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.