cancel
Showing results for 
Search instead for 
Did you mean: 

Closed Workbooks still appear to be open in VBE / memory issues

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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.

  • memory consumption of the Excel process went up, as one would expect. (checked via Task Manager)
  • the workbooks appeared in the VBE Project Explorer navigation tree, as expected.
  • after closing some of the workbooks, they were still visible in the VBE Project Explorer. Navigation of the tree structure was possible even for the "closed" workbooks.
  • according to the Task Manager the memory consumption of the Excel process stayed virtually the same, even after closing the workbooks.
  • Closing or reopening the VBE Instance did not seem to have any influence on the Memory consumption issue. I got some "out of Memory" Messageboxes from within the VBE Scope when i tried to navigate to certain parts of the "closed" workbooks in the VBE Project Explorer tree after reopening VBE. These occured with some of the worksheet leaves and the "thisworkbook" leaf, not for modules or classes. The Messages were obviously not related to the Excel process' memory consumption and the operating systems overal available memory.
  • Closing all Excel instances did free up the memory, of course.It did reset the VBE Project Explorer, too. Unloading the Analysis Add In did too.

I opened several Analysis workbooks successively and closed most of them again. Then i reopened one of the previously closed workbooks.

  • for a short time i saw the workbook twice in the VBE Project Explorer tree, while the workbook was loading. This was only temporarily. After the workbook finished loading the VBE Window was refreshed and only one node for the specific workbook remained in the Project Explorer tree. All of the "closed" workbooks were no longer shown in the VBE Project Explorer tree.
  • memory consumption of the Excel process went up with each opened workbook. It stayed virtually the same, even after most workbooks were closed again. at some point during the reopening of the workbook the memory consumption of the Excel process dropped drastically, back to an ammount that seemed ok for the now "reduced" number of workbooks open.

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

Former Member
0 Kudos

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


TammyPowlas
Active Contributor
0 Kudos

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?

Former Member
0 Kudos

Dear Tammy,

I may be an experienced end user but I can not upgrade / update my AO version.

Will have to get in contact with the IT department of my customer for whom I am programming.