Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to get data from Worksheet in Same Workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to get data from Worksheet in Same Workbook
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... "todtown" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to get data from Worksheet in Same Workbook
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 "todtown" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to get data from Worksheet in Same Workbook
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" wrote: 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 "todtown" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to get data from Worksheet in Same Workbook
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 "XP" wrote: 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" wrote: 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 "todtown" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using ADO to get data from Worksheet in Same Workbook | Excel Programming | |||
Get data from another worksheet in same workbook | Excel Programming | |||
Look up Data from Worksheet within same Workbook | New Users to Excel | |||
Look up Data from Worksheet within same Workbook | Excel Discussion (Misc queries) | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) |