Can you query a worksheet in active workbook?

[AlexanderBB]

As in sql= "some sql" and get the results back in a recordset?
If yes, are there any code examples ? I have added the Reference to allow "Set xx = New ADODB.Recordset" but no further. Do you perhaps have to open a connection to the workbook?
Using Excel 2007.
Thanks.

 

[RoryA]

Yes you can, exactly the same way as you do to a closed workbook - just specify the path to the current workbook (which must be saved). However, this will cause memory leaks if you do it repeatedly, so it's best to use a closed workbook if you can.

 

[AlexanderBB]

Thanks Rory. So it may be better to query an (not open) copy of the workbook ? And should you Open/Close the connection as required, or Open once
and only close when the workbook is Closed?