I finally got your code to (mostly) work. I do not have your queries, so I had to skip over them - therefore, they are untested.
I did make changes to your code....
There needs to be a reference set to "Microsoft Office XX.0 Object Library" where XX refers to your version number. I have A2010, so my version number is 14. My A365 version is 16.
Apparently, you have a module named "moduleImportEventSpreadsheet" with a sub named "ImportExcelSpreadsheet".
You do not need to have the module name when calling the sub.
Use
Code:
If FSO.FileExists(Nz(Me.txtFileName, "")) Then
ImportExcelSpreadsheet Me.txtFileName, "tblComplaintImportTemp"
Else
MsgBox "File not found."
End If
OR
Code:
If FSO.FileExists(Nz(Me.txtFileName, "")) Then
Call ImportExcelSpreadsheet (Me.txtFileName, "tblComplaintImportTemp") '<<-- note the parenthesis
Else
MsgBox "File not found."
End If
In any case, you do not need to use a standard module.
Here is the browse code:
Code:
Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant
Me.txtFileName = vbNullString
Set diag = Application.FileDialog(msoFileDialogFilePicker)
With diag
.AllowMultiSelect = False
.Title = "Please select an Excel Spreadsheet"
.Filters.Clear
.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"
If .Show Then
Me.txtFileName = .SelectedItems(1)
End If
End With
'btnBrowse_Click_Exit: <<-- Not needed. NO error handler implemented!!
' Exit Sub
End Sub
You have ".AllowMultiSelect = False", but then use a For...Next loop when there can only be one selection. I changed it.
The ImportSpreadsheet code:
Code:
Private Sub btnImportSpreadsheet_Click()
' Dim FSO As New FileSystemObject
Set FSO = CreateObject("scripting.FileSystemObject")
Dim d As DAO.Database
Set d = CurrentDb
' DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qryClearImportList"
If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please select a file to import."
Exit Sub
End If
If FSO.FileExists(Nz(Me.txtFileName, "")) Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblComplaintImportTemp", Me.txtFileName, True
' moduleImportEventSpreadsheet.ImportExcelSpreadsheet Me.txtFileName, "tblComplaintImportTemp"
Else
MsgBox "File not found."
End If
' DoCmd.SetWarnings (WarningsOff)
d.Execute "qryFirstNameUpdates", dbFailOnError
d.Execute "qryLastNameUpdates", dbFailOnError
d.Execute "qryCleanCustomerID", dbFailOnError
d.Execute "qryCustomerIDUpdates", dbFailOnError
d.Execute "qryNewComplaintsAppend", dbFailOnError
d.Execute "qryClearTempTable", dbFailOnError
' DoCmd.Close '<<-- what are you closing? If the form use
DoCmd.Close acForm, Me.Name
Set d = Nothing
Set FSO = Nothing
MsgBox ("Process Complete!")
' StrResponse = MsgBox("Process Complete!")
' btnImportSpreadsheet_Click_Exit: <<-- Not needed. NO error handler implimented!!
' Exit Sub
End Sub
For the DoCmd.TransferSpreadsheet command, instead of acSpreadsheetTypeExcel12 (A2007), you should use acSpreadsheetTypeExcel12Xml (A2010 and later)
See AcSpreadSheetType