I use this,
the user picks a file in the folder to use (optional)
then the code scans the folder for the 'pattern' in the string name of the file,
then loads them all into a table.
Code:
'------------------
sub btnLoads_click()
'------------------
dim vFile, vDir
dim i as integer
vFile = UserPickFile("c:\my documents\")
if vFile <> "" then
i = InStrRev(vFile, "\")
If i > 0 Then vDir = Left(vFile, i)
getFilesInDir2List vDir
endif
end sub
'------------------
Public Function UserPickFile(pvPath, Optional ByVal pvFilter)
'------------------
Dim fD As Office.FileDialog
Dim varFile
Set fD = Application.FileDialog(msoFileDialogFilePicker)
'With Application.FileDialog(msoFileDialogSaveAs)
With fD
' Allow user to make multiple selections in dialog box
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Please select one or more files"
' Clear out the current filters, and add our own.
.Filters.Clear
'.Filters.Add "Access Databases", "*.mdb"
.Filters.Add "All Files", "*.*"
.Filters.Add "Excel files", "*.xls*"
.Filters.Add "Documents", "*.doc*"
.Filters.Add "Acrobat", "*.pdf"
.Filters.Add "Image", "*.png"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .show = True Then
If .AllowMultiSelect Then
'MULTI SELECT version
'Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems
'Me.FileList.AddItem varFile
Next
Else
UserPickFile = .SelectedItems(1)
End If
End If
End With
Set fD = Nothing
End Function
'load files listed in a folder
'RETURN a text string for RowSource in value list
'------------------
Public sub getFilesInDir2List(ByVal pvDir, Optional ByVal pvPattern)
'------------------
Dim FSO, oFolder, oFile, oRX
Dim colFiles 'As Collection
Dim sTxt As String, sFile As String, sSql As String
If IsMissing(pvPattern) Then pvPattern = "."
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)
EmptyTbl "tFileList"
docmd.setwarnings false
Set colFiles = oFolder.Files
For Each oFile In colFiles
If InStr(oFile.Name, pvPattern) > 0 Then
sSql = "Insert into tFileList (NAME) VALUES ('" & oFile.Name & "')"
DoCmd.RunSQL sSql
'sTxt = sTxt & oFile.Name & vbCrLf
End If
Next
docmd.setwarnings true
Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
End Function