Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Automation Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Automation Error
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Automation Error
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$". "arjen van der wal" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Automation Error
Does the workbook have a "sheet2" (no dollar sign)? The dollar sign at the end of the sheet name is required using the ADO method because worksheets have a hidden dollar sign in the sheet name that you don't normnally see. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180404 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Automation Error
I hate myself sometimes.
I found the problem - password protection. I removed it, and the code works like it should. "joel" wrote: Does the workbook have a "sheet2" (no dollar sign)? The dollar sign at the end of the sheet name is required using the ADO method because worksheets have a hidden dollar sign in the sheet name that you don't normnally see. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180404 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '-2147221080 (800401a8)': Automation error | Excel Programming | |||
Compile error: Automation error in Excel 97 | Excel Programming | |||
Urgent!!! Run-time error '-2147024770 (8007007e)' Automation error | Excel Programming | |||
Run-Time Error'-2147221080(800401a8)': Automation Error | Excel Programming | |||
Unknown where is the problem on the Runtime error - Automation error | Excel Programming |