How to delete a web query when it's no longer needed?

[David]

Hi, All -

I have an application that uses a web query to retrieve stock quote
data. The query is created by a VBA program which looks up a ticker
list and builds the URL query string for the tickers in the list.

Strangely, I've found that if I leave the query sitting in the
spreadsheet, sometimes it starts to consume 100% CPU resources even if
I state add ".RefreshPeriod =0" to keep it from trying to update
itself.

Is there a way to get rid of the query object itself while keeping the
data it returned?

TIA,
David

 

[Norman Jones]

Hi David,

Strangely, I've found that if I leave the query sitting in the
spreadsheet, sometimes it starts to consume 100% CPU resources even if
I state add ".RefreshPeriod =0" to keep it from trying to update
itself.

See the MSKB Article # 319998
'BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)'

http://support.microsoft.com/default.aspx?scid=kb;en-us;319998&Product=x

See also:

http://tinyurl.com/dpqqd

Is there a way to get rid of the query object itself while keeping the
data it returned?

Try deleting the associated query name,
..

 

[Tom Ogilvy]

perhaps

ActiveSheet.QueryTables(1).Delete

To the best of my knowledge, this leaves the data.

If you have multiple querytables on the sheet, change the index to the
correct number or the name of the querytable.

 

[David]

Thanks, everyone. I didn't know about that memory leak bug!