on 03-06-2014 12:35 PM
Dear fellow users,
I encounter a problem when using macros in an Excel Workbook to access Analysis functions in order to refresh datasources, set filters etc.
After closing the workbook in Excel via File -> Close or clicking the "X" in its window, the Workbook is still available in the Visual Basic Editor.
If I have several files open, I will get memory issues, as the supposedly closed files still seem to take up (quite a lot of) memory.
I use AO 1.4.4.2736 with Excel 2010 32bit.
Any suggestions? Thanks in advance!
Please see attaced image. I have one open file, I close it, but is still visible in the VBE.
After unloading the Analysis Com-AddIn the file is gone, Memory is freed.
Hi,
I had same problem, So irritating. After awhile I might have 15 projects hanging around in VBE.
I found it was "adobe PDF maker .com addin" that caused the issue.
I went to "File" then "Options".. Down to "Add-ins"
In the drop-down box at the bottom, I selected "Comm Add-ins"
I had to actually remove the Adobe add-in in all together, not just un-check it, because it seems to auto load when Excel opens. After removing it.. problem of projects staying in VBE was solved
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear all,
thank you for your answers so far.
Werner:
thank you as well for trying this. Interesting to see, that this happens in other environments and AO Versions as well.
As for Martin and Victors replies:
I did some research in the internet. The relevant search items are "VBAProject closing Excel". It seems that Excel has this problem as described with various addins - not only Analysis for Office. But most of the reported cases seem to be related to COM-addins.
Some users state that this might be related to using .NET in the creation of the addin.
This is pure speculation on my side: may be there is a bug in the addin not closing references or something like that.
Fact is: that the AO COM-Addin causes this behaviour. I eliminated all other addins as possible cause.
After unloading AO everything is fine. Closing Excel has the same effect, but both solutions are not exactly feasible...
I hope someone comes up with a better solution, maybe an AO update will help in the future.
I will keep you informed if I come up with a workaround or more information on this matter.
Jan
P.S. Is this by any chance related? No idea how this site is affiliated with SAP.
http://www.stechno.net/sap-notes.html?view=sapnote&id=1585225
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Jan,
I am under the impression, that you missed a detail of my observations. In my tests there seemed to happen some kind of "cleanup" of Excels memory consumption during the time of the opening of a workbook.
As a Workaround you should be able to avoid your memory issues by opening an "additional" Analysis workbook after you closed a bunch of the memory intensive ones (perhaps even a new workbook might suffice).
Probably due to this effect, i have never faced your problem during workbook "development" or real live Analysis use. For me the conditions to force this issue did not really match our SOP. I rarely have a large number of analysis workbooks loaded simultaneously, at any given time.
Regards,
Werner
Hello Jan,
I think this is a Microsoft Excel issue, not an Analysis one:
VBA Editor doesn't close the projects for closed files in Excel 2010
Best regards,
Victor
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jan Tenner,
Most intriguing,..
Please try opening another workbook / reopening one of the closed workbooks after having closed "most" of the others. Check memory consuption and the VBE afterwards and let me know the results.
I was able to partially reproduce your problem.
I opened several Analysis workbooks successively and closed some of them after checking the memory consumption and the VBE.
I opened several Analysis workbooks successively and closed most of them again. Then i reopened one of the previously closed workbooks.
I repeated the steps again, but chose to open a different workbook, not one of the previously opened ones. I observed the same behaviour, except for the part with the "double" entry in the VBE Project Explorer.
Even without the VBE open the memory consumption issue was observable.
Test Environment:
Excel 2010 32 bit
Windows 7 64 bit
SAP Analysis Add-In 1.4.5.2826
Microsoft Office Excel Analysis Toolpack Add-In (atpvbaen.xlam / funcres.xlam)
Microsoft Office Excel "Eurowaehrungstools" Add-In (eurotool.xlam)
Microsoft Office Excel Solver Add-In (solver.xlam)
Regards,
Werner
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jan,
what you observed via VBE seems to be the same what we found out via the Task Manager.
Compare my posting from Feb. 4th: http://scn.sap.com/thread/3459138#14750340
The only solution would be closing Excel completely.
Regards,
Martin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Jan,
I am not a VB expert - what is the possibility of your downloading the latest version of Analysis Office 1.5 (patch 1) and trying this what that version?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.