Here what I use to pick existing files ...
Public Function FilePicker(strSearchPath, _
Optional strDialogBoxTitle As String = "Select a File ...", _
Optional strFileTypes As String = "Access Databases, *.MDB") As String
' Displays the Open dialog box for the user to locate
' the database. Returns the full path to data
Dim fDialog As Object
Dim varFile As Variant
' Set up the File Dialog.
Set fDialog = Application.FileDialog(3) 'msoFileDialogFilePicker
' Allow user to make multiple selections in dialog box
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = strDialogBoxTitle
' Clear out the current filters, and add our own.
Dim aFilter As Variant
aFilter = Split(strFileTypes, ";")
Dim i As Long
For i = 0 To UBound(aFilter)
Dim aSpecs As Variant
aSpecs = Split(aFilter(i), ",")
.Filters.Add Trim(aSpecs(0)), Trim(aSpecs(1))
' 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
'Loop through each file selected and add it to the text box.
For Each varFile In .SelectedItems
If .AllowMultiSelect = False Then Exit For
' Return the path and file name.
FilePicker = Trim(varFile & "")
You would add the FilePicker() code to a Standard Module (note: Module names should not share their name with any procedures).
Now, with respect to importing into a table, first you are specifying a .XLS[X] extenstions, yet indicating a TEXT file for those extensions. That this not likely the case, so you will need to have your code react to the extension returned by FileDialog. The code below would be the code behind a command button for response to a click event.
Private Sub cmbImportFile_Click()
Dim strPath As String
Dim strExtension As String
Dim strSource As String
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
strPath = FilePicker(CurDir(), , "Text Files, *.csv; Text Files, *.txt; Excel Files, *.xls; Excel Files, *.xlsx")
If Len(strPath) = 0 Then Exit Sub
strExtension = fso.GetExtensionName(strPath) 'Split(strPath, ".")(UBound(Split(strPath, ".")))
Select Case strExtension
strSource = "[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & strPath & "].[Sheet1$]"
strSource = "[Excel 12.0;HDR=YES;IMEX=2;DATABASE=" & strPath & "].[Sheet1$]"
Case "csv", "txt"
strSource = "[Text;FMT=Delimited;HDR=YES;IMEX=2;CharacterSet=437;DATABASE=" & _
fso.GetParentFolderName(strPath) & "].[" & _
fso.GetFileName(strPath) & "]"
'Create table or delete records and insert.
Dim strSQL As String
If DCount("*", "MSysObjects", "[Type] In (1,4,6) And [Name]='tblGroups'") > 0 Then
strSQL = "DELETE FROM tblGroups"
.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblGroups SELECT * FROM " & strSource
.Execute strSQL, dbFailOnError
strSQL = "SELECT * INTO tblGroups FROM " & strSource
CurrentDb.Execute strSQL, dbFailOnError
I assumed you once the import was done, you would have a local table named tblGroup with the data from the selected file in it. You can adjust the above code if that assumption does not address your need.
Hope this helps!