Query range

[Steve]

Probably an easy one, but I have been unable to find an answer:

I have a table of data in 'Sheet A' and I would like run an SQL query on
that table to return a record set that I will write to 'Sheet B'.
Should I be using ADO or is there an easier way?
If using ADO, what would the connection string be for the connection object?
Would I be pointing to the local excel file?

Any guidance is appreciated.

- Steve

 

[Tom Ogilvy]

If the workbook is going to be open, why not just use one of the filters to
copy the data.

Data => Filter => Advanced Filter or Autofilter

 

[onedaywhen]

A few points:

1) You shouldn't query an open workbook, especially not with ADO
because of a known (i.e. MS acknowledged) memory leak bug:

Microsoft Knowledge Base Article - 319998
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998

This usually isn't a show stopper because you can take a copy of the
open workbook and query the closed copy.

2) You don't usually need the overhead of a recordset object to create
a new sheet or insert rows to an existing sheet. You can use a
SELECT..INTO query to create a new sheet or a INSERT INTO..SELECT
query for an existing sheet. However, because your workbook is open
you are better off with a recordset and perhaps Excel's
CopyFromRecordset method.

3) Connection strings resource:

http://www.able-consulting.com/MDAC...oviders.htm#OLEDBProviderForMicrosoftJetExcel