I have seen a few code examples that use ADO to open a
"connection" to an Excel 2007 file. After a connection is established,
I typically see code such as this:

'=======================================
' 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
'======================================

The above code pastes data from the "rsData" recordset object
into Sheet1 starting at range A1. My question is, what if I don't
want to paste my recordset onto the spreedsheet?? Can't I
simply assign specific data values from the recordset to a Visuabl Basic
variable??

Thank you!

Robert Crandal wrote on 9/25/2010 :
I have seen a few code examples that use ADO to open a
"connection" to an Excel 2007 file. After a connection is established,
I typically see code such as this:

'=======================================
' 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
'======================================

The above code pastes data from the "rsData" recordset object
into Sheet1 starting at range A1. My question is, what if I don't
want to paste my recordset onto the spreedsheet?? Can't I
simply assign specific data values from the recordset to a Visuabl Basic
variable??

Thank you!

Yes! You don't have to write data to a spreadsheet because you can
manipulate the recordset however you want. You could, for example, dump
it into an array or parse individual records into variables.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Exactly HOW do I dump the data into an array or a variable?
I see that the Recordset has various properties and members, but
I'm not sure how to dump anything into an array.

Thank you!


"GS" wrote in message
...

Yes! You don't have to write data to a spreadsheet because you can
manipulate the recordset however you want. You could, for example, dump it
into an array or parse individual records into variables.

--

Robert Crandal brought next idea :
Exactly HOW do I dump the data into an array or a variable?
I see that the Recordset has various properties and members, but
I'm not sure how to dump anything into an array.

Thank you!


"GS" wrote in message
...

Yes! You don't have to write data to a spreadsheet because you can
manipulate the recordset however you want. You could, for example, dump it
into an array or parse individual records into variables.

--

Basically, you'd have to walk through the recordset a load each record
into an array. The problem is that each record will contain many
different values. It might be easier to dump the recordset onto a blank
worksheet first, then dump the worksheet into a dynamic array. This, if
I'm not mistaken, should result in an array of arrays wherein each
element of the array contains an array of each record's values.

You'll have to manage the order since (as I see by your connection
string) you don't have headers.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

If you want to create an array from the recordset use GetRows.

arrValues = rsData.GetRows

norie laid this down on his screen :
If you want to create an array from the recordset use GetRows.

arrValues = rsData.GetRows

Thanks! I completely forgot about that...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc