copy filenames in a folder to a table

[Geomatica]

Hi,

I am using access 2003 and would like to know how I can write the
filenames of files contained within a specified folder into a table.

This would run off a command button.

I would like the user to click the command button and then for a
dialog box to appear such that the user navigates to the folder that
contains the files that he wishes to capture the filenames of.

With this folder selected, the filenames of the files contained are
then written into a table for later use.

Any assistance would be greatly appreciated.

Kind regards,
Carl

 

[Ken Snell [MVP]]

The basic code can be found in this article, which is slightly off-topic but
not by much:

Browse to a single EXCEL File and Import Data from that EXCEL File via
TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrowseFile

In the above code, you'd replace these code lines

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, strPathFile, _
blnHasFieldNames, strWorksheet & "$"

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

with an append query and other code steps (replace generic names for table
and field):

Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile

strSQL = "INSERT INTO YourTableNameHere (YourFieldNameHere) " & _
" VALUES ( '" & strPathFile & "' )"
dbs.Execute strSQL, dbFailOnError

strFile = Dir()
Loop
dbs.Close
Set dbs = Nothing

 

[Douglas J. Steele]

Use the code at http://www.mvps.org/access/api/api0002.htm at "The Access
Web" to prompt them for the folder name.

Once you know the folder, you can use the Dir statement to determine each
file:

Dim rsCurr As DAO.Recordset
Dim strFolder As String
Dim strFile As String
Dim strSQL As String

strFolder = BrowseFolder("What Folder you want to select?")
If Len(strFolder) > 0 Then
strSQL = "SELECT FileName, FilePath FROM MyTable"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
rsCurr.AddNew
rsCurr!FileName = strFile
rsCurr!FilePath = strFolder
rsCurr.Update
strFile = Dir()
Loop
rsCurr.Close
Set rsCurr = Nothing
End If

 

[Geomatica]

Thanks for getting back guys.

I've just got into work, so I'll have a look a little later in the
day.

Many thanks. I'll let you know the progress.