Query a sheet using SQL

[jmenche]

Folks,

I have more of a conceptual question about running an SQL statement in VBA.

I have a spreadsheet that has a master data sheet and a bunch of sheets that reference it. The user is allowed to change parameters and refresh the sheets. In the past, I have built pivottables and run code to change the pivots. This is easy to do but can lead to file size problems. I toyed with using external data queries but I cannot send the file to someone not on my network because the query's path references my network and craps out.

Short story long, I would love to execute an SQL statement in VBA to query one sheet and return the results to another sheet.

Can someone provide me some generic advice on how to do this? I'm good with details so a very simple code example of how to do this would be appreciated.

Thanks

[Marcster]

How about this KB entry:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=88

Description:
This code provides the capability to enter SQL-statements in a cell and run the query against other sheets in the same workbook.

Discussion:
Using SQL directly in a worksheet as a powerful alternative to advanced filter, including the ability to join tables on two or more worksheets. The code lets you write a a complex SQL statement directly in a cell and retrive the output where you want it. This is done by referencing the ADODC.. With SQL it is also possible to query data in a closed workbook. The example file contains two examples. A query on 1 table with totals and a query that joins 2 tables. Be aware that the sample file is quite big.

[jmenche]

Thanks! I'll take a look.

[ZVI]

Please take into account that processing of open workbook data by the aid of ADO causes the memory leakage.
Such issue is explained in: support.microsoft.com/kb/319998