Thanks, well spotted. I overlooked that.

RBS

"urkec" wrote in message
...


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



You assigned a value to adoCN as if it were a string, but it is an object,
and has properties like ConnectionString and methods like Open. I made
some
changes to your code:

Public Sub QueryWorksheet()

Dim szSQL As String
Dim adoRS As Object
Dim adoCN As Object

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

adoCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No;"";"

szSQL = "Select * FROM [Sheet1$]"
adoRS.Open szSQL, _
adoCN, adOpenStatic, adLockOptimistic, adCmdText

If Not adoRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset adoRS
Else
MsgBox "No Records Returned.", vbCritical
End If

adoRS.Close
Set adoRS = Nothing
adoCN.Close
Set adoCN = Nothing

End Sub






--
urkec