Code to import Excel sheet stopped working

Lucky33

Registered User.
Local time
Today, 03:31
Joined
Sep 3, 2011
Messages
57
I have the following code to import an Excel sheet into my Access app. I wrote the code in Access 2003 a couple of years ago and now using Access 2013. It worked fine with both versions till today.
It is giving me a "Type Mismatch" error and hangs on the line in red. Any help??
"
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"
"

I have created a table (TblLuFilePath) where each user can specify his Excel file name and its path
btw, i checked and both the path and file name are correct

thanks
george :confused:
 
Code:
"TblCardsTrans", Path[B][COLOR="blue"].Value[/COLOR][/B], True, "ToAccess[COLOR="red"][B]![/B][/COLOR]"
 
Thanks but did not work
 
I see. Disambiguate:
Code:
Dim d As [COLOR="blue"]DAO.[/COLOR]Database
Dim r As [COLOR="Blue"]DAO.[/COLOR]Recordset
 
could it be in the line: "acSpreadsheetTypeExcel9"
 
Are you sure that's the line of code it highlights? Is that the line that it highlights in yellow when you hit Debug?
 
i ran a few tests now. the Debug is not showing any errors. when i execute the procedure using the button associated with, get this, before adding the DAO to the two statements, the line i mentioned turns yellow... when i add the DAO, the next line is yellow:
Set Path = r.Fields("FilePath")
 
Remember it's DAO I wrote not ADO. ADO is a different kind of recordset.
 
am sorry, it was a typo in my message that i already corrected
 
Again the field variable "Path" needs to be disambiguated with the DAO reference.
Code:
Dim Path As [COLOR="Blue"]DAO.[/COLOR]Field
 
Yeyy... worked finally... lots of debugging
many thanks
:)
 
worth to mention that i changed acSpreadsheetTypeExcel9 to:
acSpreadsheetTypeExcel12

i am not sure if this has any bearing on the outcome
 
It's just a different version of Excel, so no bearing.
The reference was the problem. There are two types, ADO and DAO, the compiler needs to know which one you want to use.
 
worth to mention that i replaced acSpreadsheetTypeExcel9 with:
acSpreadsheetTypeExcel12

am not sure if it has any bearing on the outcome
 

Users who are viewing this thread

Back
Top Bottom