Making a list of files from folders and subfolders

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I need to make a list of all files that are in multiple
formats stored on the various server drives. They need to
be converted to text strings so that they may be ordered
and sorted in a query in Access.

Is there an easy means to do this in Access 2000 using a
macro or module?

Thanks.
 
Sure, you can use the following code. The example "dirTest" just shows how
to use the other code.


Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub


Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub

You of course could add a few lines of code to write out the data to a
table, and then query/sort on that.

eg:

Sub dirTest2()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer
dim rstDir as dao.RecorcSet

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets write out the data to a table

set rstDir = currentdb.OpenReocrdSet("tblDirlist")
For i = 1 To dlist.Count
rstDir.AddNew
rstDir!Dir
rstDir.update
Next i
rstDir.Close

End Sub
 
There's a program called DDFileCatcher, that can be launched as an
Excel add-in and return file paths, extensions, size and date. It
doesn't work with Access though, but you could import the spreadsheet
into Access.

It's at www.ddfilecatcher.com
 
Back
Top