Using ADO to get data from Worksheet in Same Workbook

[todtown]

Here is an easy one for somebody. I have a workbook where one of the
worksheets contains columns of data and another worksheet is a summary
of that data. I can use formulas in the summary sheet. I can also use
something like EVALUATE(FORMULA) in VBA. But, is it possible to use
ADO to connect to the Data sheet and populate the Summary sheet using
queries against that data?

tod

 

[XP]

Hi,

I think the short answer to your question is likely "Yes" it is possible to
query an XL sheet using ADO; however, this may be limited by your knowledge
of SQL to execute the appropriate queries to return what you need in summary
form. If you know SQL well enough then that won't be a problem.

However, XL is a spreadsheet, not a database and often the data is not
consistently formatted; for example, numbers, text, formulas, and/or dates
residing in the same columns; this poses problems as ADO will only import the
predominant data type in any given column, anything else will be ignored, or
dropped from the query.

Also, the last time I checked into this (a couple years ago) the only way to
reliably query an XL sheet using ADO was to do so from another workbook (or
another app), because the workbook being queried needs to be closed when the
query is executed.

NOW, this may have changed and newer versions of ADO may have corrected this
issue, but it is something to consider. There may have been a work-around for
that issue as well, but I don't recall for sure.

If you still want to pursue it there are a lot of posts out there on ADO and
some excellent web sites that cover it (even if you just google it). Ron
DeBruin's page has some examples of ADO: http://www.rondebruin.nl/tips.htm

Hope this at least gives you a few ins/outs on it...

 

[FSt1]

hi
if the sheets are in the same workbook, i see no reason to use ADO. a
regular sub should be sufficient. and if you are using formulas to summerize
the data, why complicate it with a lot of code unless the formulas are
lacking somehow. did you leave something out of your post?????
regards
FSt1

 

[XP]

Yeah, for what it's worth, I seldom use anything more than array formulas to
produce summaries very quickly...however, there has been some discussion in
the past (in this forum) that, depending upon what you are trying to
accomplish) ADO may have superior speed in very large spreadsheets (like 50k+
rows of data)...

 

[FSt1]

yes, i have heard that too. but to date, i haven't tried that to see. I have
used ADO when working with access but i use MSQ more than ADO. the op was a
little foggy on what he was trying to do and gave no indications that he was
working on a large spreadsheet and based on his post, why bother unless it's
a learning thing. i've done that.

Regards
FSt1