Using an ADODB.Recordset in Excel 2007
 

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!

Using an ADODB.Recordset in Excel 2007
 

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

Using an ADODB.Recordset in Excel 2007
 

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.

--

Using an ADODB.Recordset in Excel 2007
 

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

Using an ADODB.Recordset in Excel 2007
 

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

arrValues = rsData.GetRows

Using an ADODB.Recordset in Excel 2007
 

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

Using an ADODB.Recordset in Excel 2007
 

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" wrote in message
...
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...

--

Using an ADODB.Recordset in Excel 2007
 

on 9/27/2010, Robert Crandal supposed :
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" wrote in message
...
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...

--

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 A1:D10 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.

--
Garry

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

Using an ADODB.Recordset in Excel 2007
 

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



"GS" wrote in message
...

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.

Using an ADODB.Recordset in Excel 2007
 

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



"GS" wrote in message
...

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Using an ADODB.Recordset in Excel 2007
 

On Sep 29, 4:22*pm, GS wrote:
Robert Crandal pretended :

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

"GS" wrote in message
...

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

Using an ADODB.Recordset in Excel 2007
 

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)



"norie" wrote in message
...



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