When you use VBA "Set ... New" to create an instance of the Word or Excel Application (not sure about Powerpoint or others) you create an instance of both of those where the New keyword actually seems to place either a "Document1" or "Book1" hidden reference (number can change, incremented by current open documents of same type and won't be shown on your taskbar) in the application's Document collection. When you subsequently start using VBA manipulation on an Excel or Word document with a "Set xxDoc = xxApp.Documents.Open(...)" that will point to an already existing document on your computer, another Document/Book reference will be placed in the applications running Document collection and that will pertain to the actual document you work on with your Open code. When you finish working on your document and save it or not, you should normally Close, Quit and set to Nothing the document and application you just worked on within the VBA code application. That removes your new document from the documents collection and SHOULD close the application BUT DOES NOT SEEM TO REMOVE THE HIDDEN "DOCUMENT1 OR BOOK1" DOCUMENT FROM THAT COLLECTION. Because of that neither Excel nor Winword can close their process AND they will continue to eat up system resources. You will have no indication this is occurring UNLESS you review the processes in Windows Task Manager. They will not show up on the taskbar. If this use of "Set ... New" has occurred numerous times you can have multiple instances of Excel or Word as running processes eating up more and more system resources and may eventually cause the system to crash OR may cause errors to occur in your VBA application where you get an unable to open file or some other such error. This happens with 2010 Access VBA and I have heard of similar problems cropping up in the more current versions of Access VBA (up to Access 2016). You can try writing a workaround where you "kill" all instances of Excel or Word that are running as part of your exit on your VBA application, BUT that will also probably "kill" without saving any other open instances of those applications on your, and your clients, computer/device that are legitimately open when the program started your VBA with the "Set ... New" statement. You could also end the process in the Windows Task Manager after insuring that no other instances of Excel or Word are open but that is a rather inelegant way of getting rid of those hidden but open documents and you shouldn't be providing that as a workaround to your clients if you want to keep them. Anyone have an elegant VBA workaround to take care of this problem?
Red Flag Submitted
Thank you for helping keep Tek-Tips Forums free from inappropriate posts. The Tek-Tips staff will check this out and take appropriate action.
Reply To This Thread
Posting in the Tek-Tips forums is a member-only feature.