How can I list the files in a directory and sub directory

[felixc36]

Hi,

I have this project where i must read all the files havin a specific extention
(*.mdb for example) in a directory and subdirectories.

I was wondering if that is possible.

What ultimately I am looking for is building a procedure that would have a
root directory and an extention as parameters and then would create a
recordset having the path and filenames of all the files having the extention
in the directory and it's sub directories.

Is that a feasable thing in Access 97?

You can answer me here or at my e-mail address (e-mail address removed).
(please remove the NoSPAM in the address).

Thank you

 

[Alan Z. Scharf]

When you say read, do you mean read data from them or get a listing.

The System File Object will handle many file-related operations.

Alan

 

[felixc36]

Thank you for answering....

What I want to do is first to capture the path and flename and utlimately read
the contents of the files for processing.

For example I have the following file structure:
C:\--|
MyDir------------Agency1 ---- 2002--- File1.dbf, file2.dbf
| | --------- 2003---File1.dbf
| |----------- 2004---File1.dbf
|
|--------------------Agency2 ---- 2002---File1.dbf, file2.dbf
| |-------- 2003---File1.dbf
|
|---------------Agency3 ---- 2000--- File1.dbf, file2.dbf
|----------- 2001---File1.dbf, file2.dbf
|------------ 2002--- File1.dbf, file2.dbf

I want to pass as a parameter MyDIR and The DBFextension to a procedure.

What I would want is for the procedure to create a recordset capturing the
path of each files having a dbf extension in a variable.

"C:\Mydir\Agency1\2002\File1.dbf"
"C:\Mydir\Agency1\2002\File2.dbf!"
"C:\Mydir\Agency1\2003\File1.dbf!"
"C:\Mydir\Agency1\2004\File1.dbf!"
"C:\Mydir\Agency2\2002\File1.dbf!"
...

... and so on.

 

[Miaplacidus]

I have a similar problem, I can use filesearch to get the listing of files
and subdirectories. What Ai want to do first is read the other file
attributes into a table and later, based on file attributes, opent the files
and search for keywords or other information. The end result will be to
create an index of files on the drive with contacts etc.

 

[Albert D. Kallal]

Sure, you can use the following code of mine....

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 can of course change the first dir command to only return *.dbf fields.

And, if you need to put the data into a reocrdset, you could go somthing
like:

dim rst as dao.recordset
.....
....
set rst = currentdb.OpenReocrdSet("tblDir")

For i = 1 To dlist.Count
rst.Add
rst!MyFileName = dlist(i)
rst.Update
Next i

Anyway, the above code shell should get you something to work with....

 

[Miaplacidus]

Thanks, that worked, but I would have never glommed it alone. I used that to
create a list of files in a DB. Then I went back and read the list and opened
each file to create a summary of what is in it and also to pick up all the
other file properties. I used Auto summary in word to create a short summary,
and that works OK. I'm still working on a method for other file types. I'd
like to, for example open a PPT file and step through the text boxes looking
for words that are used frequently (other than prepositions). I need a
similar method for other file types, like excel.

Anybody got suggestions, I'm listening.

 

[Jim]

This code is awesome. It worked with no modification and solved my problem
with just a few add ons.

Thanks,

Jim Arnold
St Michaels MD
=======================