Yes, I tested and noticed the same.
Not sure now what the trouble is.
Any particular reason why you don't want to set the ADO reference?
Using early binding will be faster and easier.

RBS

"JMay" wrote in message
...
Still getting at line:
adoRS.Open szSQL, adoCN, 0, 1

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

Code to date: (with your suggestions)

Option Explicit
Public Sub QueryWorksheet()

Dim adoCN As Object
Dim adoRS As Object
Dim szConnect As String
Dim szSQL As String
Dim strFile As String

strFile = "C:\Documents and Settings\Jim May\My Documents\" & _
"MyExcelFormulas\My-ADO_Plan\MyXLS_DataSource_file.xls"

Set adoCN = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")

'Create the connection string
adoCN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties='Excel 8.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$]"

adoRS.Open szSQL, adoCN, 0, 1

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

End Sub



"RB Smissaert" wrote:

You will need something like this (not tested)

Dim adoCN As Object
Dim adoRS As Object
Dim szConnect As String
Dim szSQL As String
Dim strFile As String

strFile = "C:\Documents and Settings\Jim May\My Documents\" & _
"MyExcelFormulas\My-ADO_Plan\MyXLS_DataSource_file.xls"

Set adoCN = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")

'Create the connection string
adoCN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties='Excel 8.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$]"

adoRS.Open szSQL, adoCN, 0, 1


Note that you can't use the ADO constants like adOpenForwardOnly if there
is
no reference to ADO.
So you have to replace them with the literal values, as above. You can
get
these values by setting the ADO reference under
Tools, References and then do for example
Msgbox adOpenForwardOnly
and run that.
You can then remove the reference again.

RBS


"JMay" wrote in message
...
User-defined type not defined is actually what I get
at the line Set rsdata = New adoRS - after doing a Debug Compile
VBProject

"JMay" wrote:

Thanks RB, I did as you instructed.
First when I reran code - I got:

Set rsdata = adoRS

Set rsdata 'ERROR Variable not defined, or the like.
so I changed the line to

Set rsdata = New adoRS ' which the book showed before and I removed
it

'But still it errored @ the New adoRS stage..

Thanks for your help.



"RB Smissaert" wrote:

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