Import filenames with Shell DIR command

  • Thread starter Thread starter Alan Z. Scharf
  • Start date Start date
A

Alan Z. Scharf

I need to import filenames from a directory into an Access table.

How can I do this with the Shell command using DIR?

I've been using File System Object, but that is too slow getting 4,000 file
names over my netowrk.

Thanks.

Alan

___________________________
Alan Z. Scharf
GrapeVine Systems
New York City
 
How to Add Directory File Names to an Access Table:

Create a table named tblDirectory with 2 fields:
FileName (Text 250)
FileDate (Date/Time)

Call the code below by pressing Ctrl-G to open the debug window and type:
GetFiles("c:\windows\")

Paste this code into a regular module:

Sub GetFiles(strPath As String)
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)
'Loop through the balance of files
Do
'check to see if you have a filename
If strFile = "" Then
GoTo ExitHere
End If
strDate = FileDateTime(strPath & strFile)
rs.AddNew
'to save the full path using strPath & strFile
'save only the filename
rs!FileName = strFile
rs!FileDate = strDate
rs.Update

'try for next filename
strFile = Dir()
Loop

ExitHere:
Set rs = Nothing
MsgBox ("Directory list is complete.")
End Sub
 
Joe,

Thanks very much.

It works beutifully. Now I will test it pulling filenames over my network.

Regards,

Alan
 
Joe,

The looping method of getteing 4,000 filenames over my client's network is
still too slow, takes about ten minutes.

Do you know a way using shell and DIR to get the long filenames into an
Access table? That way I only have to get the test result of the DIR over
the network.

I can handle the parsing of the resultant DIR format into usable data. I
just need at least to get the results into an Access table.

Thanks.

Alan
 
Joe,

Please forget my request about shelling to DIR.

I finally figured it out, making a text file from DIR /b to get just
filenames and then importing to table.

Thanks again for your help!

Regards,

Alan.
 
Back
Top