Trying to import Excel into Access, but no records are being moved

[Seth Schrock]

I'm using the following code to import data from Excel to Access:

Public Sub ImportMatrix()
Dim db As DAO.Database
On Error GoTo Error_Handler
 
Set db = CurrentDb
 
DoCmd.Hourglass True
 
db.TableDefs.Refresh
DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="tblMatrix", _
    FileName:="\\ftcbank1\docs\sschrock\My Documents\Database Stuff\FTC Morgage Rate Database\Mortgage Matrix.xls", _
    HasFieldNames:=False, _
    Range:="MatrixTable"
db.TableDefs.Refresh
 
 
Exit_Procedure:
    Set db = Nothing
    DoCmd.Hourglass False
 
    Exit Sub
 
Error_Handler:
    'Call ErrorMessage(Err.Number, Err.Description, "modImport: ImportMatrix")
    'TSCs_ReportUnexpectedError "ImportMatrix", "modImport", "Custom info"
    Resume Exit_Procedure
    Resume
 
 
End Sub
 

I have a named range within the excel file titled MatrixTable and from looking on the MSDN website, I should be able to use the name of the range. I don't get any errors, but no data gets transferred. tblMatrix is a pre-existing table. I'm not sure what to try at this point.

[TheSmileyCoder]

I have used the following bit of code without incident:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_CustomCommentImportReplies", strImportFile, , "Comments!A:L"

This works to import columns A through L on the sheet named Comments.

I remember that I often spend a fair amount of time tinkering with Excel exports to get them to work just as I want. Have you tried adding a exclamation before Matrixtable, so that:

Range:="!MatrixTable"

I am really just guessing here.

[neelsfer]

I have had so many issues using TheSmileyCoder code here above, because my field types (ie text or number etc) were different in Excel and Access for the same fields. If they are the same, then it should import in no time.

[TheSmileyCoder]

Most excel sheets I issue for use as remove data entry, I lock down before issue, so that users can only type in certain fields, and do certain actions. This greatly limits the number of things that can go wrong, although it is not always an option.

I also usually import to a temp table first, to weed out any import errors before appending to the live data.

Are you sure the import range is typed correctly? What happens if you deliberately type a non-existing importrange such as DonaldDuck? Does it give an error message then?

[Seth Schrock]

I tried moving it to my computer so that there is no network problems and I put in the actual range so I now have

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMatrix", _
            "C:\Databases\Mortgage Matrix.xlsm", False, "NewSheet!A1:E126"

I still get no error message (I did try DonaldDuck as the range and didn't get an error message that time either), but still, no records are imported. I made sure that all of the data types in the excel file are numbers and that they match the access table.

[Seth Schrock]

I figured it out. Very dumb mistake. I had my table missing a field that was in the excel sheet. I ended up having the excel sheet have field names and I was able to use the named range just like I had in my OP. Thanks Smiley for your help.