Hello,
I need to create a form that will allow a user to click a button to bring up a dialog box that will allow them to import the contents of an excel file or text file into an existing table. Could anyone point me in the direction of some examples to get me started? I've tried searching and haven't really come across what I'm looking for. I've found one that shows how to do something similar with a text file, but I can't seem to get it working for what I want.
Any help would be appreciated.
Thanks...
Full Version: Import From Excel/text File Into A Table
Hi,
Take a look at the RunCommand method and the acCmdImportAttachExcel or acCmdImportAttachText arguments in Access Help.
Just my 2 cents...
Take a look at the RunCommand method and the acCmdImportAttachExcel or acCmdImportAttachText arguments in Access Help.
Just my 2 cents...
That's a good suggestion, but unfortunately for what I need it has to be a little more automated than that (sorry, I guess I should have been more specific). Right now I'm messing around with this code sample I found and it seems like I can get it working for the Excel portion, but I'm not too sure how to tweak it so that it would automatically work for text or Excel files.
CODE
Private Sub Command0_Click()
Dim FileBrowse As Office.FileDialog
Dim varFile As Variant
Dim sField As String
Set FileBrowse = Application.FileDialog(msoFileDialogFilePicker)
With FileBrowse
.AllowMultiSelect = False ' <= Set to true if you need to select more than one text file
.Title = "Please select a file to import"
.Filters.Clear
.Filters.Add "Text Files", "*.xls"
.Filters.Add "Text Files", "*.xlsx"
.Filters.Add "All Files", "*.*"
If .Show = True Then
sField = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImportDelim, , "tblGroup", sField, 1
MsgBox "Transfer Complete!"
Else
MsgBox "Operation Cancelled"
End If
End With
End Sub
Dim FileBrowse As Office.FileDialog
Dim varFile As Variant
Dim sField As String
Set FileBrowse = Application.FileDialog(msoFileDialogFilePicker)
With FileBrowse
.AllowMultiSelect = False ' <= Set to true if you need to select more than one text file
.Title = "Please select a file to import"
.Filters.Clear
.Filters.Add "Text Files", "*.xls"
.Filters.Add "Text Files", "*.xlsx"
.Filters.Add "All Files", "*.*"
If .Show = True Then
sField = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImportDelim, , "tblGroup", sField, 1
MsgBox "Transfer Complete!"
Else
MsgBox "Operation Cancelled"
End If
End With
End Sub
I see... I think you need to loop through the choices and check the file extension, then use either TransferSpreadsheet or TransferText depending on the result.
Just my 2 cents...
Just my 2 cents...
Here what I use to pick existing files ...
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.
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!
CODE
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
With fDialog
' 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.
.Filters.Clear
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))
Next i
' 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
Next
End If
End With
' Return the path and file name.
FilePicker = Trim(varFile & "")
End Function
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
With fDialog
' 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.
.Filters.Clear
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))
Next i
' 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
Next
End If
End With
' Return the path and file name.
FilePicker = Trim(varFile & "")
End Function
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.
CODE
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
Case "xls"
strSource = "[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & strPath & "].[Sheet1$]"
Case "xlsx"
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) & "]"
End Select
'Create table or delete records and insert.
Dim strSQL As String
If DCount("*", "MSysObjects", "[Type] In (1,4,6) And [Name]='tblGroups'") > 0 Then
With CurrentDb
strSQL = "DELETE FROM tblGroups"
.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblGroups SELECT * FROM " & strSource
.Execute strSQL, dbFailOnError
End With
Else
strSQL = "SELECT * INTO tblGroups FROM " & strSource
CurrentDb.Execute strSQL, dbFailOnError
RefreshDatabaseWindow
End If
End Sub
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
Case "xls"
strSource = "[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & strPath & "].[Sheet1$]"
Case "xlsx"
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) & "]"
End Select
'Create table or delete records and insert.
Dim strSQL As String
If DCount("*", "MSysObjects", "[Type] In (1,4,6) And [Name]='tblGroups'") > 0 Then
With CurrentDb
strSQL = "DELETE FROM tblGroups"
.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO tblGroups SELECT * FROM " & strSource
.Execute strSQL, dbFailOnError
End With
Else
strSQL = "SELECT * INTO tblGroups FROM " & strSource
CurrentDb.Execute strSQL, dbFailOnError
RefreshDatabaseWindow
End If
End Sub
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!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.