Import Excel file into a table

[lucky33]

I have upgraded to Access2013 from 2003 and I have an application that I also upgraded to accdb. Because of a blackout yesterday, my application got corrupted and became unusable. So I opened a backup two weeks old, and when I tried to re-enter the lost data, I was faced with another problem. I use the following code to import a range in an Excel file with one sheet:



'setting variables to get the file path from table TblLuFilePath
Dim d As Database
Dim r As Recordset
Dim Path As Field
Set d = CurrentDb()
Set r = d.OpenRecordset("TblLuFilePath")
Set Path = r.Fields("FilePath")
'import range named ToAccess from the Excel file defined in the previous code
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TblCardsTrans", Path, True, "ToAccess"

the code is freezing at the line in red and giving a run-time error 13, type mismatch
Any suggestions please ???
George

[June7]

I don't understand use or need for a field object variable but that isn't cause of issue. Don't see problem with the offending line code.

You open a recordset of all table records. The code looks only at the first record of the recordset. Does this table have only one record?

An alternative to opening recordset is DLookup.

Dim Path As String
Path = DLookup("FilePath", "TblLuFilePath")

Or no variables at all.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TblCardsTrans", DLookup("FilePath", "TblLuFilePath"), True, "ToAccess"

[lucky33]

thank you for your reply and solution. you are right, the table has one record only, as i am giving the users choice to place and name the excel file anyway they want. since you gave me a working alternative, i am not going to dwell on why the offending line code is not working anymore
many thanks again