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

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

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

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

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

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

Need to enclose all the extended properties in single quotes, so:

adoCN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties='Excel 8.0;HDR=Yes';"

Note that you need Excel 8.0, even if you have a higher Excel version.

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

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

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