Listing Files/Directories 
Author Message
 Listing Files/Directories

Hi,

I am looking for some VBA script for MS Access that will allow me to list
files from a certain directory and then save them to a table. I have found a
database that is like internet explorer but it lists all the information in
a list box but I am unable to copy and paste that into a Table/Word/Excel.
Can anyone point me to where this may be found or have any ideas I would
really appreciate your help.

I have versions of Access 97 amdnd XP so any for either will help me.

Regards,
Hayden.



Tue, 31 Aug 2004 03:10:58 GMT  
 Listing Files/Directories
Hayden,

Try something like the following....

==========
Dim rs as recordset
Dim db as database
Dim strFile as string
Set db = currentdb()
Set rs = db.OpenRecordset("TableNameToUpdate", dbOpendynaset)
strFile = Dir$("C:\testdir\")
While strFile <> ""
    rs.addnew
        rs("TableFieldToUpdate") = strFile
    rs.update
    strFile = Dir$()
End While
========

Double check my syntax, especially concernning the Addnew part.  But I
believe this is close.  Note that this will not put the directory name with
each file, just the filename itself.  If you want the directory name change
the one line to rs("TableFieldToUpdate") = "C:\testdir\" & strFile.  Of
course, it helps if the directory name is in a variable.  HTH

David Gnatek
SoftQuest Technologies

(248) 674 - 2044


Quote:
> Hi,

> I am looking for some VBA script for MS Access that will allow me to list
> files from a certain directory and then save them to a table. I have found
a
> database that is like internet explorer but it lists all the information
in
> a list box but I am unable to copy and paste that into a Table/Word/Excel.
> Can anyone point me to where this may be found or have any ideas I would
> really appreciate your help.

> I have versions of Access 97 amdnd XP so any for either will help me.

> Regards,
> Hayden.



Tue, 31 Aug 2004 03:28:41 GMT  
 Listing Files/Directories
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


Quote:
> Hi,

> I am looking for some VBA script for MS Access that will allow me to list
> files from a certain directory and then save them to a table. I have found
a
> database that is like internet explorer but it lists all the information
in
> a list box but I am unable to copy and paste that into a Table/Word/Excel.
> Can anyone point me to where this may be found or have any ideas I would
> really appreciate your help.

> I have versions of Access 97 amdnd XP so any for either will help me.

> Regards,
> Hayden.



Tue, 31 Aug 2004 12:21:55 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Selecting files in many directories, use of file list box

2. Directory Listing of files to include files in the subdirectories

3. directories and sub-directories in the list

4. Help: displaying hidden directories in directory list box

5. directories and sub-directories in to a list

6. List of files in directories

7. List Directories to a File Macro Help

8. Macro to list the files in a directory?

9. Printing a Directory of Files List

10. List-Box for Files/Directory in Word-VBA

11. List-Box for Files/Directory in Word-VBA

12. Getting list of files in a directory

 

 
Powered by phpBB® Forum Software