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, Im 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 Im missing?

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

Regards - James