Before anything else put Option Explicit a the top of that module and then
do Debug, Compile VBAProject.
You will then see that it can't compile, so it can't run.
Get it to compile first and take it from there.

RBS


"JMay" wrote in message
...
I'm trying to implement the example of "Querying MS Excel Workbooks,
Page 411-412 of WROX's EXCEL 2002 VBA Programmer's Reference.

I use xl 2003 SP2

I entered the original Code below but have (at this point butchered it a
bit
- by
commenting out certain lines - Since I found googling a few lines
suggested
by Bob Phillips regarding Late Binding) - Currently The Code is BOMBING at
the Line marked *****

and I am getting the error:

3709 -- The connection cannot be used to perform this operation. It is
either closed or invalid in this context.

Thanks in advance for any help.

Public Sub QueryWorksheet()

'Dim rsdata As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim adoRS As Object, adoCN As Object

Set adoRS = VBA.CreateObject("adodb.recordset")
Set adoCN = VBA.CreateObject("adodb.connection")



'Create the connection string
adoCN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Jim May\My
Documents\MyExcelFormulas\My-ADO_Plan\MyXLS_DataSource_file.xls;" & _
"Extended Properties='Excel 11.0;HDR=No';"
' Query based on the worksheet name.
'szSQL = "SELECT * FROM [MyXLS_DataSource_file$]"
' Query based on a sheet-level range name.
'szSQL = "SELECT * FROM [MyXLS_DataSource_file$MySheetLevelName]"
' Query based on a specific range address.
'szSQL = "SELECT * FROM [MyXLS_DataSource_file$Sheet3!A10:F16]"
' Query based on a book-level range name.
'szSQL = "SELECT * FROM ????????[MyXLS_DataSource_file$]"

szSQL = "SELECT * FROM [MyXLS_DataSource_file$]"

Set rsdata = adoRS
rsdata.Open szSQL, adoCN, adOpenForwardOnly, _ ******
adLockReadOnly

' Check to make sure we received data.
If Not rsdata.EOF Then
Sheet1.Range("A1").CopyFromRecordset rsdata
Else
MsgBox "No Records Returned.", vbCritical
End If
' Clean up our Recordset object
rsdata.Close
Set rsdata = Nothing

End Sub