Public Sub List_Files()
'-----------------------------------------------------------------
'Sub to list files in a folder
'Requires Microsoft Scripting Runtime Reference (Tools, References Menu)
'-----------------------------------------------------------------
'Declare any variables and constants
Dim fsoFolder As Folder
Dim fso As FileSystemObject
Dim intRowNumber As Integer
Dim strFileName As String '***Create your Excel File FIRST
Dim xlApp As Excel.Application
Set fso = New FileSystemObject
'Specify the path to the files
Set fsoFolder = fso.GetFolder("MyFolderPath") 'Full Folder Path Here
'Specify the Excel file being used to list them
strFileName = "MyExcelFilePath" 'You could set this based on the value in your form text box
'e.g. strFileName = Me!MyTextBox
'***Relies on you having the full path in there!
'Instantiate a new instance of Excel and then hide it
Set xlApp = New Excel.Application
xlApp.Visible = False
'Open the output file
xlApp.Workbooks.Open strFileName
'Set the counter
intRowNumber = 1
'Loop through the files in the folder
For Each file In fsoFolder.Files
'Activate the Excel sheet we're using to list the files
xlApp.Worksheets(1).Activate
'Goto the first data cell in the receiving sheet
xlApp.Goto Reference:="R" & intRowNumber & "C1"
With xlApp
'Add in the filename
.range("a" & intRowNumber).Select
.range("a" & intRowNumber).Value = file.NAME
'Save the book
.ActiveWorkbook.Save
End With
'Increase intRowNumber by 1 to reference the next (blank) row
intRowNumber = intRowNumber + 1
Debug.Print file
'Move to the next file
Next file
'Release Excel
xlApp.Quit
'Import the spreadsheet
DoCmd.TransferSpreadsheet acImport, , "tblFileList", strFileName, False
'Cleanup
Set fsoFolder = Nothing
Set fso = Nothing
Set xlApp = Nothing
End Sub
