Web Queries

[Pelham]

Dear All

I have a spreadsheet with about 450 different but similar Web Queries
in it which
have been created with a VBA Macro. The Macro simply goes to about 450
different but similar websites and extracts Japanese real estate data
into a worksheet for me to analze later. However, once all the data is
copied to the worksheet, the Web Queries remain in the worksheet which
takes up too much added memory. Does anyone know how to remove all the
Web Queries once the Macro has stopped running?

I have tried CTRL-A and then right clicking to remove the Web Query but
it does not seem to remove all of them...

 

[NickHK]

One solution would be to have a single web query.
Update the .ConnectionString from a list, call .Refresh False and process
the resulting data.

This may be slower because you forcing all to be synchronous, but your 450
current queries may be using asynchronous download, depending how you have
it set up.
But you only have the single QueryTable set up.

Or, if you want keep all 450 queries, you can edit the Properties of each,
checking the "refresh data on file open" and 'remove external data from
worksheet before saving".
If you record these changes in macro for a query, you can then apply them to
all queries in code.

NickHK