Extracting file names to a table

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

Guest

I have two folders with a list of pending documents to be actioned.

I want to create a summary of the outstanding files and need help creating a
table of the current files listed in the two folders.

i have very limited VB knowedge and need help.

I am using Access 2K

Thanks
 
You will need to set a reference in VBA to Microsoft Scripting Runtime and
Microsoft DAO 3.6

Here is some code that gets the file names in one folder, you will want to
do it twice to get both folders.

Sub try2()
Dim fso As Scripting.FileSystemObject
Dim theCurrentFolder As Folder
Dim curFiles As Files
Dim fileItem As File
Dim fname As String
Dim qry As DAO.QueryDef
Dim strQuery As String
Dim path As String

path = "path/to/folder"

'create an instance of the FileScriptingObject and assign it to the
variable fso
Set fso = CreateObject("Scripting.FileSystemObject")

'create an instance of the Folder object and assign it to the variable
theCurrentFolder
'pass the path of the folder as a parameter

Set theCurrentFolder = fso.GetFolder(path)

'now the Files collection of the folder object are exposed
Set curFiles = theCurrentFolder.Files

'create a query so you can run queries later
Set qry = CurrentDb.CreateQueryDef("", "Select * from tablename")

'iterate through the collection of file names...
For Each fileItem In curFiles
fname = fileItem.Name

'running a query to insert the filename into the table called TableName

strQuery = "INSERT INTO TableName(FileName) SELECT '" & fname & "'"
qry.SQL = strQuery
qry.Execute
Next

'clean up all files
Set fso = Nothing
Set theCurrentFolder = Nothing
Set curFiles = Nothing
Set fileItem = Nothing
Set qry = Nothing


End Sub

Please let me know if you need more assistance.
 
Thanks, works a treat.

hmadyson said:
You will need to set a reference in VBA to Microsoft Scripting Runtime and
Microsoft DAO 3.6

Here is some code that gets the file names in one folder, you will want to
do it twice to get both folders.

Sub try2()
Dim fso As Scripting.FileSystemObject
Dim theCurrentFolder As Folder
Dim curFiles As Files
Dim fileItem As File
Dim fname As String
Dim qry As DAO.QueryDef
Dim strQuery As String
Dim path As String

path = "path/to/folder"

'create an instance of the FileScriptingObject and assign it to the
variable fso
Set fso = CreateObject("Scripting.FileSystemObject")

'create an instance of the Folder object and assign it to the variable
theCurrentFolder
'pass the path of the folder as a parameter

Set theCurrentFolder = fso.GetFolder(path)

'now the Files collection of the folder object are exposed
Set curFiles = theCurrentFolder.Files

'create a query so you can run queries later
Set qry = CurrentDb.CreateQueryDef("", "Select * from tablename")

'iterate through the collection of file names...
For Each fileItem In curFiles
fname = fileItem.Name

'running a query to insert the filename into the table called TableName

strQuery = "INSERT INTO TableName(FileName) SELECT '" & fname & "'"
qry.SQL = strQuery
qry.Execute
Next

'clean up all files
Set fso = Nothing
Set theCurrentFolder = Nothing
Set curFiles = Nothing
Set fileItem = Nothing
Set qry = Nothing


End Sub

Please let me know if you need more assistance.
Click to expand...
 
Back
Top