Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Leak
I am using a VBA macro in Excel 2003 to download data from a server.
The macro opens up a workbook containing equations (functions) in an Add-In provided by the company who's software is running on the server. So the process is: 1. Opening the workbook containing the equations pointing to the Add In, the functions are updated (i.e. data downloaded from server). 2. Copy the results, pastes it in a new workbook (as values), save the new workbook and close it. This process is repeated. I cannot edit or see the code of the Add In (password protected). The Add In apparently has a memory leak, because my memory is just filling up (after a +- 30 cycles, I cannot open a new workbook any more - "Out of memory"). I am sure it is not my code. If I replace all the equations downloading data from the server with "Rand()", i.e. having the same number of cells with the same amount of data, my memory usage stays constant for endless(?) cycles. So, what I need is some sort of workaround. Closing Excel clears the memory. I tried just de-selecting the add-in and then reselecting it (Tools--Add-Ins), this does not work. So I think what I need is somehow to automatically close Excel, re-open it and cary on with the macro. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Leak
If you are convinced it is a memory leak then you can create an instance of
Excel for you add-in/processing to work with. Close this instance every 10-15 runs and reopen another instance. e.g. from an Excel VB Const RUNS_REQUIRED As Long = 100 Dim InstanceCount As Long Const MAX_RUNS As Long = 15 Dim RunCount As Long Dim XLApp As Excel.Application For InstanceCount = 1 To RUNS_REQUIRED / MAX_RUNS Debug.Print InstanceCount Set XLApp = New Excel.Application 'load the add-in, as is not loaded by default with automation For RunCount = 1 To MAX_RUNS 'processing code Debug.Print "Process : " & RunCount Next XLApp.Quit Next You would obviously need to calculate the number of loops correctly. NickHK "Sakkie" wrote in message ups.com... I am using a VBA macro in Excel 2003 to download data from a server. The macro opens up a workbook containing equations (functions) in an Add-In provided by the company who's software is running on the server. So the process is: 1. Opening the workbook containing the equations pointing to the Add In, the functions are updated (i.e. data downloaded from server). 2. Copy the results, pastes it in a new workbook (as values), save the new workbook and close it. This process is repeated. I cannot edit or see the code of the Add In (password protected). The Add In apparently has a memory leak, because my memory is just filling up (after a +- 30 cycles, I cannot open a new workbook any more - "Out of memory"). I am sure it is not my code. If I replace all the equations downloading data from the server with "Rand()", i.e. having the same number of cells with the same amount of data, my memory usage stays constant for endless(?) cycles. So, what I need is some sort of workaround. Closing Excel clears the memory. I tried just de-selecting the add-in and then reselecting it (Tools--Add-Ins), this does not work. So I think what I need is somehow to automatically close Excel, re-open it and cary on with the macro. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Leak
Thanx for the advice Nick. I finally got around to testing this. It
works, but it doesn't... I open a new instance of Excel as suggested: Set xlAppNewInstance = CreateObject("Excel.Application") xlAppNewInstance.Visible = True Then I open the file that uses the Add-Inn in the new instance: xlAppNewInstance.Workbooks.Open (HourTemplateFile) This works fine, now the next problem is the Add-In is not automatically loaded in the new instance of Excel. I tried to open it the same way I opened the workbook above: xlAppNewInstance.Workbooks.Open (AddInFile) This does not seem to work, no idea why, It does not make the functions of the Add-In available. If I interrupt the VBA code here and in the new instance of Excel manually open the file "AddInFile), the functions are available and everything works fine if I allow the code to continue. Does all of this solve the "Memory leak" problem? Interestingly enough, when the new instance of excel is closed, the memory used is NOT returned; it is only freed when the procedure that opened the new instance ends. Hmmm Any ideas on how to get that Add-in to work without manually interfering would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Leak
Hi Nick
Once again, thanks for the good advice. I got a new instance of Excel open, opened my workbook and installed the add-in (for some strange reason I had to set the installed parameter of the ad-in to false and then to true, otherwise it would not work): oAddin = xlAppNewInstance.AddIns.Add(AddInFile) oAddin.Installed = False oAddin.Installed = True I had to set the sisplay alerts to false, so I do not get interupted by messages during the "installation" of the add-in: xlAppNewInstance.DisplayAlerts = False Now my next problem is that I need to set the calculation mode to manual for the new instance of excel. I tried the following: xlAppNewInstance.Calculation = xlCalculationManual But I get a "Run-time error 1004: Method 'Calculation' of object '_Application' failed". I do not realy understand this, as the "xlAppNewInstance.DisplayAlerts = False" works instead of "Applicatio.DisplayAlerts = False", but the "xlAppNewInstance.Calculation = xlCalculationManual " does not? Sakkie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Leak
OK, don't worry, I solved it. I should have applied the advice given in
the article you pointed me to for this problem as well: "Note that a workbook is added prior to calling the Add method in this example. The Add-in Manager in Excel is not available unless there is at least one visible workbook open. Without this line of code, you receive one of the following error messages: Run-time error '1004': Unable to get the Add property of the AddIns class -or- Run-time error '1004': Add method of addins class failed. " http://support.microsoft.com/kb/280290 So all I did is I first opened my workbook and then set calculation to manual, works like a charm :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL 2007 - Out of Memory - memory leak/bug? | Excel Discussion (Misc queries) | |||
Memory leak query | Excel Discussion (Misc queries) | |||
Memory Leak | Excel Programming | |||
Memory Leak | Excel Programming | |||
Memory Leak using ADO 2.6 | Excel Programming |