Using an ADODB.Recordset in Excel 2007

[Robert Crandal]

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!

 

[GS]

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.

 

[Robert Crandal]

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]

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!

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.

 

[norie]

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

arrValues = rsData.GetRows

 

[GS]

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...

 

[Robert Crandal]

Hi Gary....

I tried running the command below just for the heck of it on a
workbook that contains 20 worksheets or so.... this operation
took a long time and I saw the mouse pointer turn into an hourglass.
After about 4 to 5 minutes I eventually got some sort of out of
memory error message. Does the GetRows() method try to
load the entire workbook into a varriant variable??

As a side note, my query string is pretty simple (I think). It looks
like this:

szSQL = "SELECT * FROM [Sheet1$A1:A1]"

I only wanted to retrieve the contents of cell A1 from the workbook, so
I dont understand why my query takes so long and eventually runs out of
memory or something. Do you know what happened?

Thank you!

 

[GS]

Hi Gary....

I tried running the command below just for the heck of it on a
workbook that contains 20 worksheets or so.... this operation
took a long time and I saw the mouse pointer turn into an hourglass.
After about 4 to 5 minutes I eventually got some sort of out of
memory error message. Does the GetRows() method try to
load the entire workbook into a varriant variable??

As a side note, my query string is pretty simple (I think). It looks
like this:

szSQL = "SELECT * FROM [Sheet1$A1:A1]"

I only wanted to retrieve the contents of cell A1 from the workbook, so
I dont understand why my query takes so long and eventually runs out of
memory or something. Do you know what happened?

Thank you!

norie laid this down on his screen :

Thanks! I completely forgot about that...

--

GetRows loads the table (in this case it's the entire sheet) into an
array of arrays. So if your only looking for the value in A1 then don't
use GetRows. Otherwise, it should work fine as long as you don't
specify a single cell range. IOW, specifying A110 should return 10
rows of data. Specifying A1:A1 shouldn't return any rows and so may
very well be why you're getting the mem issue.

If A1 is the only value you want then just specify "SELECT * FROM
[Sheet1$A1]"

If you want to dump the entire sheet or a specified (multi-row) range
into an array then GetRows seems to be the quickest way.

 

[Robert Crandal]

Hmm, that command didnt seem to work. It only works if I
use SELECT * FROM "[Sheet1$A1:A1]."

 

[GS]

Robert Crandal pretended :

Hmm, that command didnt seem to work. It only works if I
use SELECT * FROM "[Sheet1$A1:A1]."

If A1 is the only value you want then just specify "SELECT * FROM
[Sheet1$A1]"

If you want to dump the entire sheet or a specified (multi-row) range into
an array then GetRows seems to be the quickest way.

Okay. I don't use single cells with ADO and so I wouldn't know this.
However, I certainly wouldn't use GetRows deliberately if I was only
collecting data from a single cell.<g> Not sure why you would do
that...!

 

[norie]

Robert Crandal pretended :

Hmm, that command didnt seem to work.  It only works if I
use SELECT * FROM "[Sheet1$A1:A1]."

If A1 is the only value you want then just specify "SELECT * FROM
[Sheet1$A1]"
If you want to dump the entire sheet or a specified (multi-row) range into
an array then GetRows seems to be the quickest way.

Okay. I don't use single cells with ADO and so I wouldn't know this.
However, I certainly wouldn't use GetRows deliberately if I was only
collecting data from a single cell.<g> Not sure why you would do
that...!

--
Garry

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

I agree with Garry if it's one cell what's the point in GetRows, but I
would take it a wee bit further?

Why use ADO to get one value from a workbook??

 

[Robert Crandal]

I'm actually implenting a VBA script which performs a search
through several hundred Excel workbooks, and I want this
search to run as quickly as possible. Opening and closing
a couple hundred macro enabled workbooks seems kinda
time consumining, so I figured why not use ADO to run the
search more quickly.

Gary made the following comment in a past post to me:

- "The point I was trying to make is that using the methods demonstrated
in Rob's examples doesn't require opening anything. I don't understand
why you'd want to open all those files when it's not necessary AND it
will slow down your procedure to a crawl and then possibly an out of
memory crash."

He was basically saying that I can use ADO to avoid opening "all those
files when it's not necessary". I just figured I would experiment with
Gary's suggestion and see if it runs faster than the alternative.

8)



I agree with Garry if it's one cell what's the point in GetRows, but I
would take it a wee bit further?

Why use ADO to get one value from a workbook??