Directory list to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to Access and I would like to import a directory list into a table.
I think I have the first part figured out. I can use a batch file or DOS
commands to create a CSV file with the current list. The DOS commands I think
should work are as follows:
del /q "C:\my files\directory.csv"
dir /b c:\ > c:\directory.csv

This deletes any existing directory.csv file then generates a new
directory.csv file that contains the current directory list. However, the
path (C:\my files\) are not in the CSV document.

Now my question is how do import the CSV file and add the path (C:\my files)
to the beginning? I also want to make the table hyperlink to the files.
 
You're deleting C:\my files\directory.csv and creating c:\directory.csv:
which do you really want?

What is it you're trying to create hyperlinks to: your directory.csv file,
or each of the files contained in that file?

Note that using dir is guaranteed to give you files in C:\my files unless
you explicitly make that the current directory:

It might be easier to do what you're trying to do using VBA, rather than
shelling to DOS. If you want the names of all of the files in C:\my files
stored in a table MyTables (which has already been created, and has a field
name File in it), try something like this:

Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strSQL = "DELETE FROM MyTables"
CurrentDb.Execute strSQL, dbFailOnError

strFolder = "C:\my files\"
strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
strSQL = "INSERT INTO MyTables(File) " & _
"VALUES ('" & strFolder & strFile & "')"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir()
Loop

Note that the assignment to strSQL inside the loop, exagerated for clarity,
is:

strSQL = "INSERT INTO MyTables(File) " & _
"VALUES ( ' " & strFolder & strFile & " ' ) "
 
Back
Top