 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2006Workbook wont go away! & OUT OF MEMORY error
I have written a class that when instanced, represents product information or a product line. I am working with MS Office 2003 and Windows XP Pro. Inside the Class_Initialize() .. I have the following code: *************************************************************** Set ProductList = Workbooks.Open("C:\Program Files\MLO Tools\" & FileName) ... ... other code ProductList.Close SaveChanges:=False Set ProductList = Nothing ************************************************************** So I open a file, strip out the info, then close the file. Even after the code closes the file, the workbook reference and all its objects (sheet1, thisworkbook) stay in the VBA project explorer. If I run this program 5 times, there are 5 references to the workbook; Each reference displays a dialog "OUT OF MEMORY" if I try to open it. Then the OUT OF MEMORY issue prevails, evenif I try to type new code. Does anybody have any clue why this is happening. John, It's very likely that you are not cleaning up all of your objects. Everything must be closed and then set to Nothing. I can't tell whether you are using VB 6 or VB.NET, but if your using VB.NET you will probably want to also do garbage collection and you will likely have to clean up the COM references. Here is some C# code that shows how to do that. The double cleanup was actually recommended in a Microsoft document that I can't find right now. private void ShutDownExcel() { if (mExcelApp != null) { mExcelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcelApp); mExcelApp = null; } // Clean up memory so Excel can shut down. GC.Collect(); GC.WaitForPendingFinalizers(); // The GC needs to be called twice in order to get the // Finalizers called - the first time in, it simply makes // a list of what is to be finalized, the second time in, // it actually the finalizing. Only then will the // object do its automatic ReleaseComObject. GC.Collect(); GC.WaitForPendingFinalizers(); } Also notice the call to System.Runtime.InteropServices.Marshal.ReleaseComObject() you'll want to look into that. Good luck, Kim  Signaturedigipede - Many legs make light work. Grid computing for the real world. http://www.digipede.net http://krgreenle.blogspot.com I am actually using Office 2003 VBA, which the help Menu defines as Microsoft Visual Basic 6.3. I had the same thoughts about forgetting to set objVars to nothing, but it is clear to me that my code closes the workbook then sets the object pointer to nothing. Is there a better way to open an existing workbook then: set mywb = workbook.open("path") Also, if I close down excel and restart it, all the references in the VBA project window are gone. I just don't want to have to close down excel everytime I want a clean system, plus, if I am doing something wrong, I would like to stop it. You know? John > John, > [quoted text clipped - 36 lines] > > Kim John, It's not just the workbook, it's also any worksheets, ranges, etc... basically anything that you used "Set" with. I've been running distributed Excel computations which require a clean shutdown, and the only problems I've encountered with Excel not shutting down is when I didn't clean everything up. Also, did you call Application.Quit()? That's about the extent of anything I can think of. Good luck. Kim Signaturedigipede - Many legs make light work. Grid computing for the real world. http://www.digipede.net http://krgreenle.blogspot.com John (and Kim), Not fully cleaning up can indeed cause this problem, but apparently so too can some other conditions which, apparently, are not fully understood. I think you'll find that the *objects* associated with what I call your "phantom" workbook -- eg, ThisWorkbook -- can no longer be selected in the VBE. The "out of memory" messages are, at least, innocuous. What I most object to is that, although you can exceute code in the phantom modules, attempting to *remove* them crashes Excel. There were a couple posts on this subject a few years ago, but I don't think anyone then claimed to understand precisely what is happening or how to work around the problem. I posted a situation that creates a similar condition a few weeks ago (opening a workbook via Application.Run) to see if there were any more recent insights. Unfortunately, there were no responses. I guess the really useful resources, who used to participate more heavily here, no longer do -- undrestandably. Keith Keith, I've seen the memory problems when there are multiple Excel processes running. This will happen if Excel is not properly shutdown (ie. the clean up stuff I was talking about). There can only be x number of Excel processes running before memory problems occur. I have observed that when launched as a GUI, Excel will only create one process no matter how many workbooks are opened. This includes opening a workbook from the Desktop, not just directly from Excel. This is not the case when Excel is launched programmatically. Each time an Excel object is created another process is launched. I've had about five active Excel processes before I've run into the memory problem, my assumption is that this will change based on the available resources. When I was testing my code, I killed the unwanted Excel processes before each test until I had my code shutting down cleanly. This helped me moved forward without wasting my time on a problem that I had no control over and was only a side effect of my shutdown problems. Kim Signaturedigipede - Many legs make light work. Grid computing for the real world. http://www.digipede.net http://krgreenlee.blogspot.com Understood. But my point is that the OP's issue can arise in situations that don't involve potentially multiple Excel processes. Here's my example. An Excel workbook, started from the desktop, includes the following in a standard module: Set Wkb = Workbooks.Open(Path) Call Application.Run("'" & Wkb.Name & "'!Install") Call Wkb.Close Set Wkb = Nothing Path is the full path of an existing, closed workbook containing nothing but one public sub, Install, which just shows a MsgBox (for my attempting to isolate the problem). Wkb is closed. Wkb is set to Nothing. And still the "phantom" VBE project persists. I believe that the Application.Run is somehow creating a reference within Excel that is not cleared and that I cannot (see how to) clear either. (I think I recall hitting a similar problem when the opened-then-closed workbook, Wkb here, creates an OnAction reference, but the OnAction reference is *not* to a routine in Wkb. Unfortunately, this was a while ago. Still, if memory serves, that's interesting because the OnAction linkage is similar to Application.Run.) If your experience can shed any light on what's happening here, I'd love to hear ideas. Keith John (and Kim), Not fully cleaning up can indeed cause this problem, but apparently so too can some other conditions which, apparently, are not fully understood. I think you'll find that the *objects* associated with what I call your "phantom" workbook -- eg, ThisWorkbook -- can no longer be selected in the VBE. The "out of memory" messages are, at least, innocuous. What I most object to is that, although you can exceute code in the phantom modules, attempting to *remove* them crashes Excel. There were a couple posts on this subject a few years ago, but I don't think anyone then claimed to understand precisely what is happening or how to work around the problem. I posted a situation that creates a similar condition a few weeks ago (opening a workbook via Application.Run) to see if there were any more recent insights. Unfortunately, there were no responses. I guess the really useful resources, who used to participate more heavily here, no longer do -- undrestandably. Keith Wow. As an itermediate programmer, I am confused. Basically, as I think I understand it, there is really nothing I can do about it. I was thinking, and forgive me if I am way off base, that maybe if the workbook was created and save in a lower version then 2003, maybe I need to use a "RunAutoMacro" e.g. With ActiveWorkbook .RunAutoMacros xlAutoClose .Close End With ??? > John (and Kim), > [quoted text clipped - 17 lines] > > Keith Wired (and presumably the OP), The problem you met can be created in "complex" situations (eg, Kim's situation when Excel is launched programmatically). But it also can crop up in fairly straightforward applications -- as you've discovered. I've not yet uncovered a useful work-around(beyond stop and restart Excel which really doesn't help). Therefore, the question becomes: Is there a different way to do what you (we) are trying to do. Here are a couple guesses: (1) This problem shouldn't arise just because you're opening a file, doing stuff, and closing the file. I don't know what "stuff" you're doing (just stripping out information? but then why save changes?). Nevertheless, the first step probably is to check your code in a standard module in a clean workbook. If that still creates a phantom VBE entry, then what's in "stuff" becomes critical and your best chance to get some help is to repost with more detail. (2) If, on the other hand, this exercise does not create a phantom VBE entry, then I'd guess the culprit is that you're doing these things within a Class_Initialize routine, rather than a standard module. Maybe it's enough to let Class_Initialize call a routine in a standard module. Hopefully, this can help, Keith Hello! I'm experiencing similar problem (same resulting problem, different cause). I have a managed shared Excel add-in which when loaded causes Excel to display phantom entries in VBAProject explorer. I've implemented some event handlers for Workbook and Worksheet Application events in mine add-in. When I remove these event handlers there are no phantom entries left behind. I've tried implementing a version of Kims' suggestion in BeforeClose event handler but this didn't work. Note: the difference is, I was cleaning Workbook object - Wb not the mExcelApp itself since I need it: private void mExcelApp_WorkbookBeforeClose(Microsoft.Office.Interop.Excel.Workbook Wb, ref bool Cancel) { if(Wb != null) { while(System.Runtime.InteropServices.Marshal.ReleaseComObject( Wb ) > 0 ); Wb = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } Anyway I've discovered following: 1)I start Excel with my add-in and add several workbooks, let's say 3 in total 2)I open Visual Basic Editor(Alt+F11) and I'm able to see all three workbooks in Project explorer 3) Now I close one workbook but it stays displayed in Project explorer 4) I close Project explorer and then close the other two workbooks (no more left in Excel) 5) I open Visual Basic Editor again and I still see one phantom workbook (the one I closed first) in Project explorer This means when I closed my first workbook, true release didn't happen (probably because VBA editor had referenced the same workbook). Any help would be appreciated. Regards, Vlado I'm not going to be any help, because I've more-or-less given up attempting to resolve the issue of phantom workbooks. (Actually, in my case, the workbook and worksheet objects sort-of disappear -- in the sense that I no longer can select these in the VBE. But phantom code module(s) remain.) I'm also convinced that VBA is not releasing a (code) reference. As I mentioned above I'm actually able to execute this phantom code -- so it's clearly around. However, when I attempt to "remove" the module, Excel crashes. All of what I'm reporting is from operating in Excel itself and using VBA only, so it's not the same situation as yours. But I suspect it's even more troubling since, in my case, there's no issue of multiple instances of Excel. I hope someone can do more. Sorry, Keith Hi! I will probably ignore this for now since I see no solution for it on the horizon. I can only recommend my users not to keep VBA editor open while they are closing workbooks. I'll monitor this thread in case some workarounds/solutions emerge and will post my solution if I find one. Regards, Vlado |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.