Is this code similar to the code you are trying to execute?
Code:
Dim strTable As String
Dim strFilePath As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Locate a Excel file to Import"
.ButtonName = "Import"
.Filters.Clear
' .Filters.Add "Excel", "*.xlsx"
.Filters.Add "Excel", "*.xls"
.InitialFileName = "C:\Test"
.InitialView = msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Sub
End If
'Save the first file selected
strFilePath = Trim(.SelectedItems(1))
End With
'strTable = "tbl1_PartPricingDataAggregation"
strTable = "tblTest"
'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, strTable, strFilePath, True
You need to place your code in an event handler like a click event for a Command Button. Here is an example for a Click event for a Command Button named Command0
Code:
Private Sub Command0_Click()
Dim strTable As String
Dim strFilePath As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Locate a Excel file to Import"
.ButtonName = "Import"
.Filters.Clear
' .Filters.Add "Excel", "*.xlsx"
.Filters.Add "Excel", "*.xls"
.InitialFileName = "C:\Test"
.InitialView = msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Sub
End If
'Save the first file selected
strFilePath = Trim(.SelectedItems(1))
End With
'strTable = "tbl1_PartPricingDataAggregation"
strTable = "tblTest"
'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, strTable, strFilePath, True
end Sub
You can call a Public function from a click event too.
In order for your function to work it needs to be in a Standard Module and the module cannot share the same name as the Public Function.
Code:
Public Function fMy_Export()
Dim strTable As String
Dim strFilePath As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Locate a Excel file to Import"
.ButtonName = "Import"
.Filters.Clear
' .Filters.Add "Excel", "*.xlsx"
.Filters.Add "Excel", "*.xls"
.InitialFileName = "C:\Test"
.InitialView = msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Sub
End If
'Save the first file selected
strFilePath = Trim(.SelectedItems(1))
End With
'strTable = "tbl1_PartPricingDataAggregation"
strTable = "tblTest"
'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, strTable, strFilePath, True
End Function