Excel VBA - Web Query causes Excel to run out of Memory
 

Happy Holidays...

I am writing a VBA script that uses Excel to get external
data from a web site in the form of a web query. This
technique works fine, except the process (which cycles
through several thousand times) comes to grinding halt
when Excel runs out of memory?¦ and I have a lot of
memory on the PC.

I am not saving any variables from the web query
(i.e. .SaveData = False etc.) and I have the following
lines in the VBA script:

X = ActiveSheet.QueryTables.Count
ActiveSheet.QueryTables.Item(X).Delete
Range("a1:r120").Select
Selection.Clear ?~deletes data from query
Unload (QueryTables)
Application.Goto Reference:="ExternalData_"
Selection.Delete Shift:=xlUp

In other words, I?Tm trying to clear everything I can
identify after capturing certain data from the query
before cycling to another input variable to reduce the
size of the file and minimize memory usage?¦ but to no
avail.

I have not seen any Knowledgebase articles specifically on
this subject. Is this unavoidable, or is there a VBA
command and/or Excel technique I?Tm missing?

Thanks in advance to anyone who can help or steer me in
the right direction.

Regards - James