Why getting an error when try to import Excel data into a table

I have the following code in an onclick event of a command button.  But I am getting an error:  "An expression you entered is the wrong data type for one of the arguments."  ????

Here's the code:

Private Sub cmdImportAndClean_Click()
On Error GoTo Err_cmdImportAndClean_Click

    If MsgBox("This function will import the sales data Excel file you have selected. Do you want to continue?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    DoCmd.Hourglass True

    'Import Excel file into tblTempImport
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", Me.FileList, True
 
    'Use tblTempImport to add records to table named tblImportedExcelData
    Dim db As Database
    Set db = CurrentDb

    db.Execute "apndtblqryImportRecords", dbFailOnError
    
    'Run query to delete records from tblTempImport
    db.Execute "delqryDeleteRecordsFromtblTempImport", dbFailOnError
    
    MsgBox "The Excel file data has been imported into the database."
    
    Else
    
    MsgBox "Import Function Cancelled"

    DoCmd.Close , "frmImportSalesData", acSaveNo
    
    End If

    DoCmd.Close , "frmImportSalesData", acSaveNo
    DoCmd.Hourglass False
 
Exit_cmdImportAndClean_Click:
    Exit Sub
    
Err_cmdImportAndClean_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportAndClean_Click
 
End Sub

Open in new window

SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
which line is raising the error?
SteveL13Author Commented:
I don't know.  I just get the error message.
Rey Obrero (Capricorn1)Commented:
replace your codes with these

Private Sub cmdImportAndClean_Click()
On Error GoTo Err_cmdImportAndClean_Click

    If MsgBox("This function will import the sales data Excel file you have selected. Do you want to continue?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    DoCmd.Hourglass True

    'Import Excel file into tblTempImport
    'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", Me.FileList, True
 
     DoCmd.TransferSpreadsheet acImport, 10, "tblTempImport", Me.FileList, True
 
 
    'Use tblTempImport to add records to table named tblImportedExcelData
    Dim db As Database
    Set db = CurrentDb

    ' db.Execute "apndtblqryImportRecords", dbFailOnError
 docmd.setwarnings 0
    docmd.openquery "apndtblqryImportRecords"
docmd.setwarnings -1	
   
   'Run query to delete records from tblTempImport
    'db.Execute "delqryDeleteRecordsFromtblTempImport", dbFailOnError

 docmd.setwarnings 0
    docmd.openquery "delqryDeleteRecordsFromtblTempImport"
docmd.setwarnings -1	
    
    MsgBox "The Excel file data has been imported into the database."
    
    Else
    
    MsgBox "Import Function Cancelled"

    DoCmd.Close , "frmImportSalesData", acSaveNo
    
    End If

    DoCmd.Close , "frmImportSalesData", acSaveNo
    DoCmd.Hourglass False
 
Exit_cmdImportAndClean_Click:
    Exit Sub
    
Err_cmdImportAndClean_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportAndClean_Click
 
End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SteveL13Author Commented:
Am getting the same error.
Rey Obrero (Capricorn1)Commented:
comment this line

' On Error GoTo Err_cmdImportAndClean_Click

run the code again and see which line is raising the error

take note of the error number and message
SteveL13Author Commented:
Error 2498

And this line is highlighted in yellow when I debug

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempImport", Me.FileList, True
SteveL13Author Commented:
Also happens with

DoCmd.TransferSpreadsheet acImport, 10, "tblTempImport", Me.FileList, True
Rey Obrero (Capricorn1)Commented:
what is the value in Me.FileList ?

post it here
SteveL13Author Commented:
C:\Users\Steve\Desktop\SalesAll.xlsx

Which is coming from this code in another onclick event of another command button:

Private Sub cmdFileDialog_Click()

' This requires a reference to the Microsoft Office 11.0 Object Library.  I have 15.0 in place.

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant

   ' Clear the list box contents.
   Me.FileList.RowSource = ""

   ' Set up the File dialog box.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' Allow the user to make multiple selections in the dialog box.
      .AllowMultiSelect = True
            
      ' Set the title of the dialog box.
      .Title = "Select One or More Files"

      ' Clear out the current filters, and then add your own.
      .Filters.Clear
      .Filters.Add "Excel Worksheets", "*.xlsx"


      ' 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 that is selected and then add it to the list box.
         For Each varFile In .SelectedItems
            Me.FileList.AddItem varFile
         Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Sub

Open in new window

Rey Obrero (Capricorn1)Commented:
is the item selected in the FileList ?
Gustav BrockCIOCommented:
Problem is that Access reads the first five or so records to determine that data types. If the sheet at some later point contains date that can't fit, it either errors out or display #Num or #Error.

There is no other to solve this than trying to link or import the spreadsheet manually.
This will either highlight the missing data or fill the offending fields with #Error or #Num. This will show you what to handle.

I always link spreadsheets, initially using the wizard which allows you to specify the data types for the fields. Some you may have to specify as Text even though they are not. Then I have one simple select query that does nothing but convert those fields to what I need, say converting text dates to true date values.
Then, if needed, I perform the import using this query.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
You are absolutely correct.  Here is what I did.  I created an import spec and the data came into the table just fine.  But what I see, which I didn't see before is data like this...

9.7814767933e+012

Maybe I should just use the import spec and let the data come in as it will?
Gustav BrockCIOCommented:
That's a Double: 9781476793300.
You can use it as. If that causes trouble, try:

DoubleValue: CDbl([YourField])

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.