Import filenames with Shell DIR command

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 Fallon
Access MVP



"Alan Z. Scharf" <ascharf@grapevines.com> wrote in message
news:%23kAw7mjpDHA.3844@tk2msftngp13.phx.gbl...
> 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
>
>

Those CurrentDB references should really be cached :-)

TC


"Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
news:#Idfn#jpDHA.3040@TK2MSFTNGP11.phx.gbl...
> 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 Fallon
> Access MVP
>
>
>
> "Alan Z. Scharf" <ascharf@grapevines.com> wrote in message
> news:%23kAw7mjpDHA.3844@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
>

Joe,

Thanks very much.

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

Regards,

Alan

"Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
news:#Idfn#jpDHA.3040@TK2MSFTNGP11.phx.gbl...
> 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 Fallon
> Access MVP
>
>
>
> "Alan Z. Scharf" <ascharf@grapevines.com> wrote in message
> news:%23kAw7mjpDHA.3844@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
>

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

> ___________________________
> Alan Z. Scharf
> GrapeVine Systems
> New York City
>
>

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.