Error Using DAO Recordset in Excel

[ejc00]

I am trying to SELECT information from a sheet in Excel into a DAO Recordset. When I run the code on one computer, it works perfectly fine. When I run the code on another computer with a similar load and all of the same references checked, I get the following error:

Run-time error 3011: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

What is odd is that the computer which errors out recognizes the sheet if I try to activate or select it, but it doesn't seem to like the syntax [Sheet1$], which is required to run the SQL statement. I have also tried 'Sheet1$', which is acceptable. Neither works.

Here is the code:

Sub ProcessRecordset(Destination As Worksheet)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFile As String
Dim strSQL As String
Dim wsSource, wsDestination As Worksheet

On Error GoTo 0

strFileName = Application.ActiveWorkbook.Name

Set db = OpenDatabase(strFileName, False, False, "Excel 8.0;HDR=Yes;")

strSQL = "TRANSFORM Sum(Volume) As SumOfVolume"
strSQL = strSQL & " SELECT CorpName, Cat"
strSQL = strSQL & " FROM [Sheet1$]"
strSQL = strSQL & " WHERE Prod = 'Product'"
strSQL = strSQL & " GROUP BY CorpName, Cat"
strSQL = strSQL & " ORDER BY CorpName, Cat, ProdDate"
strSQL = strSQL & " PIVOT ProdDate;"

Set rst = db.OpenRecordset(strSQL)

Destination.Range("A8").CopyFromRecordset rst

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub


Any help would be greatly appreciated. It is frustrating that it works perfectly fine on one computer and does not work on another computer that is nearly identical (though clearly different in some way). We have checked the MDAC version to both and applied the most recent Microsoft updates to both computers... this does not fix the issue.

 

[Fazza]

Welcome. I don't know why it fails on one machine. If suitable, instead of the [Sheet1$] reference can you give the source data a defined name and use that instead in the SQL? Such as, in the source file add a defined name such as "tblData" that covers the required data range. [Note, not however a dynamic named range such as using COUNTA or INDEX.] HTH, Fazza

 

[ejc00]

Tried using a named range and received the same message...

 

[Fazza]

With the early binding, are the references are set from VBA for DAO?

 

[Fazza]

PS. Try closing down and rebooting...

 

[ejc00]

Have tried rebooting. Regarding the references, I have DAO and ADO both set. I have also now tried doing it using both DAO and ADO, and both work on my computer, but neither works on the other computer I am trying to use the code on. It still has a problem with the reference to [Sheet1$]. Any more ideas???

 

[Fazza]

Have you saved the file on the second computer before running the code? If not, I think that would lead to an error. Untested.

Maybe is not relevant, but looking at the code it could be tightened up a bit. Such as there is "Dim strFile as String" but strFile is not used in the code; strFileName is used but is not dim'd. "Dim wsSource, wsDestination as Worksheet" dims wsSource as a variant rather than "Dim wsSource as Worksheet" and neither seem to be used anyway.

 

[ejc00]

Thanks for the assistance, and the advice. I was able to resolve the issue by not writing the data to a temporary sheet within my file. My original code was copying data from a source file into the spreadsheet and then writing that copied data into a recordset. I changed the code to write the data from the source file directly to the recordset, and this eliminated the error. I'm glad to have solved the issue, but it is still odd to me that the spreadsheet didn't seem to recognize the temporary sheet that I was creating when using the recordset syntax (esp. since I was able to set it as an active sheet through VBA using standard VBA syntax).

Thanks again.