-
Never have worked with inserting info directly into an excel file, but here's how to do it with access. Assuming you have a Database and table with column named "fileName" created already.
Code:
' define the necessary variables
strPathToFolder = ' insert folder path
strDBPath = ' insert path to database
strTableName = ' insert table name
' instantiate all necessary items
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPathToFolder)
Set objFolderContents = objFolder.Files
Set objConnection = CreateObject("Adodb.Connection")
Set objRecordset = CreateObject("Adodb.Recordset")
' open data
objConnection.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & strDBPath
objRecordset.Open strTableName, objConnection, 3, 3, 2
' now loop through the contents of the folder
For Each objFile in objFolderContents
strFileName = objFile.Name
' and add each to the table
objRecordset.AddNew
objRecordset("fileName") = strFileName
objRecordset.Update
Next
' clean up
Set objRecordset = Nothing
Set objConnection = Nothing
Set objFSO = Nothing
Set objFSO = Nothing
It may be ugly, but it works!
-
I don't have Access on this PC. It conflicts with some other software we use. How about moving the files to another directory without knowing the names of the files.
This would get me by until I can come up with something else.
Thanks a bunch,
JO
