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
|