memory leak issue with vba, any help appreciated

[PTProgram]

I have a spreadsheet which has a bunch of input cells. Lots of calculations on the sheet which takes the cell inputs and looks up time series data based on these inputs. Data to be queried from is in a 10MB excel file. So, instead of doing vba lookup which always chokes due to file size, the excel file is imported to access where the size goes down dramatically.

With each iteration in the vba code it:

1) changes a cell in excel
2) refreshes ranges in excel which import data from access query. The access query is based on the cell in excel.
3) once refreshed, excel does a series of calculations (user defined functions) and just regular calculations. A couple cells have the output of the results I am interested in.
4) excel then copies these values from output results to a new worksheet.
5) next iteration changes cell as in step 1, and goes through above steps pasting in the new worksheet once row below.

The problem is that while the vba code works fine, after 100-150 iterations the memory used by excel balloons up to 160MB and gives a memory 7 error where I cant even save the documents. My guess, and this is done through some testing is that the refresh range part causes the ballooning. If I block this part of the code out the memory issue disappears but the data is all the same (no refresh all the copies are of the same values based on iteration 1)

Here is the code..thanks in advance.

-----------------------------------------------------------------------------
Sub CreateMiniSummaryTableNEW()
Dim x As Long, oRange As Range
Set oRange = Workbooks("Output Table.xls"). _
Worksheets("Mini Output").Range("C5")
For x = 500 To 520
Range("G5").Value = x
Application.Run "'Master Pair Trade Data Query.xls'!Module1.RefreshRange"
' This refreshed the range of excel ranges which are linked
' to an access query run which uses cell values from excel
' see seperate code below

oRange(x).Resize(, 19).Value = Range("CC10:CU10").Value
Next x
End Sub
---------------------------------------------------
Sub RefreshRange()
Application.Goto Reference:="ModDateRange"

Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:="PricePERangeTicker1"
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:="PricePERangeTicker2"
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.Goto Reference:="IndexData"
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("D18").Select

End Sub

 

[Andrew Poulsom]

Maybe you should try using ADO:

http://www.experts-exchange.com/Applications/MS_Office/Q_20129091.html