ADO Automation Error

[EricG]

I have a very simple Workbook_Open routine that is supposed to grab some data
from another Excel workbook. I'm not doing anything with the data yet,
because I can't get past the automation error I'm getting. The error is:

"The Microsoft Jet database engine could not find the object 'Sheet2$'.
Make sure the object exists and that you spell its name and the path name
correctly."

Sheet2 definitely exists and there is data on it. Does anyone see an error
in my code below, or is there some other source for the error. The error
occurs on the line marked with **************.

Thanks,

Eric

Option Explicit

Private Const masterFile = "D:\data\thisUser\Desktop\MasterFile.xls"

Private Sub Workbook_Open()
Dim conData As New ADODB.Connection
Dim rstAssigns As New ADODB.Recordset
'
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String
'
With conData
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & masterFile & _
";Extended Properties=Excel 8.0;Persist Security Info=False"
.ConnectionTimeout = 30
.Open
End With
'
strSelect = "SELECT * FROM [Sheet2$]"
'
On Error GoTo Oops
'
rstAssigns.ActiveConnection = conData
rstAssigns.Source = strSelect
rstAssigns.Open '***************** This does not work.

On Error GoTo 0
'
Do While Not rstAssigns.EOF
For intCount = 0 To rstAssigns.Fields.Count - 1
' Do stuff with the data here...
Next
rstAssigns.MoveNext
Loop
'
conData.Close
Exit Sub
'
Oops:
Debug.Print "Oops! Something went wrong."
Debug.Print Err.Description
End Sub

 

[arjen van der wal]

Hi Eric,

I have a spreadsheet with a similar routine. In the section where you have
the error occur I have mine setup as follows (I've used the names from your
example):

rstAssigns.Open strSelect, conData, adOpenStatic, adLockReadOnly, adCmdText

Your sheet name reference does look correct.

Here's the whole routine as I had set it up. It's structured a bit different
from yours, but that shouldn't be an issue.

Sub QueryExcelSupersheet()

'create the connection string
Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=K:\ADO\SUPERSH.xls;" & _
"Extended Properties=Excel 8.0;"

'create the sql query
Dim MyQuery As String

MyQuery = "SELECT * " & _
"FROM [Today$] "

'create the recordset
Dim MyRS As ADODB.Recordset
Set MyRS = New ADODB.Recordset

'open the recordset
MyRS.Open MyQuery, ConnectionString, adOpenStatic, adLockReadOnly,
adCmdText

Sheets("xl data").Activate
ActiveSheet.Range("A1").CopyFromRecordset MyRS

MyRS.Close
Set MyRS = Nothing

End Sub

Hopefully this helps.

 

[EricG]

Thank you for your reply.

I took your code exactly, changed the file path and name to mine, and
changed the sheet name to mine. I get the same error that I got with my
code. :-(

It still bombs on the "open" statement, complaining the it can't find the
object "Sheet2$".

 

[joel]

Does the workbook have a "sheet2" (no dollar sign)? The dollar sign a
the end of the sheet name is required using the ADO method becaus
worksheets have a hidden dollar sign in the sheet name that you don'
normnally see.

 

[EricG]

I hate myself sometimes.

I found the problem - password protection. I removed it, and the code works
like it should.