Get list of files in directory

[Jonathan Blitz]

How can I get a list of all the files in a specified directory from VBA
code?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[M.L. Sco Scofield]

Look up the Dir function in VBA help. There's even some sample code there.

Good luck.

Sco

 

[StCyrM]

Hi Jonathan

The following code should do this for you.

Here is some code that will return a full list of all the files:

Hope this helps

Maurice


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 could easily add the code to insert each value into a table like:


dim rstRecs as dao.recordset

set retrecs = currentdb.OpenRecordSet("tblFiles")

For i = 1 To dlist.Count
Debug.Print dlist(i)
rstRecs.AddNew
rstRecs!FileName = dlist(i)
rstrecs.Update
Next i

rstrecs.close
set rstrecs = nothing

 

[Jonathan Blitz]

Thanks.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."