Import User Selected Excel to Access Table

[scuddersm]

Hello All,

I am in need of some help on this one. I am trying to import a "user selected" excel file into an access table. I have been able to some of what I need using.

DoCmd.RunSavedImportExport ("Import-CPIMS-Belt-Report")

But this only works on my machine becuase I created the Saved Import.
However, because multiple users will be accessing this database on multiple computers the file path/location will likely change everytime. I need a way for the Import to pull the selected file and path from the user selected file. I think the solution lies in TransferSpreadSheet but I can't get it to work. This is what I have so far.

Private Sub btnCPIMSImport_Click()
Dim CPIMS As Object
Dim ExcelFilePath As String
Set CPIMS = Nothing
Dim CPIMSTable As String
ExcelFilePath = ""
 
'Open file dialog and wait for user to select CPIMS Report .xls or .xlsx
    Set CPIMS = Application.FileDialog(msoFileDialogOpen)
    CPIMS.Title = "Select CPIMS Belt Report"
    CPIMS.AllowMultiSelect = False
    CPIMS.Show
    With CPIMS
 
'If user does not select file open msgbox and exit sub
    If CPIMS.SelectedItems.Count > 0 Then
    ExcelFilePath = .SelectedItems(1)
    Else
    MsgBox "You didn't select a file"
    Exit Sub
    End If
    ExcelFilePath = .SelectedItems(1)
    End With
 
' Delete current records in tblCPIMS
    CPIMSTable = "Delete * from tblCPIMS;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL CPIMSTable
    DoCmd.SetWarnings True
 
' Import New MOL detailed list into tblCPIMS
    DoCmd.TransferSpreadsheet ,10 ,"tblCPIMS", ExcelFilePath, True
End Sub

The last line of code is what I am having problems with. I recieve an error meesage stating that tbl CPIMS does not have 'F1' and various other errors as I change the code.
In the Locals window ExcelFilePath = "D:\Documents and Settings\Scudder\My Documents\MALS-14_Database_Belt_Report.xlsx"
Any help on this is greatly appreciated.

Thanks,
Scott

 

[scuddersm]

Figured it out.

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "tblCPIMS", ExcelFilePath, True