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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Winword AND Excel process still running after Quit and set to Nothing
- Thread starter RolandR
- Start date
- Thread starter
- #3
- Thread starter
- #4
combo,
Although the app shows by putting your code just before the Quit and after the Doc close (and using a Msgbox right after setting to visible to pause the processing), the Word application didn't show anything but a non-Doc word screen with no questions being asked. Continuing by clicking the MessageBox OK seemed to then Quit the app and set it to nothing as the VBA code was meant to do. However, when single clicking, or even hovering over, on any Word doc without opening it in Windows Explorer, shows Winword becoming a process in the Windows Task Manager and using up system memory to the tune of about 50 MB. This Winword instance will persist even after you open any other Word document and then exit the Word application using your normal way to open a Word item (double clicking it in Windows explorer) HOWEVER, there was one added benefit I noticed by using your app.visible line of code. Eventually, Winword will pop up the window for the hidden document preventing it from closing and freeing up the memory after you have opened up other Word documents. It came up as a "Document50" blank document in my case and didn't allow you to make any changes to it using the WinWord menus, BUT allowed you to close the application running by clicking on the close application icon (upper right little x) and that then would close the system process Winword instance shown still running in Windows Task Manager. Very perplexing.
Although the app shows by putting your code just before the Quit and after the Doc close (and using a Msgbox right after setting to visible to pause the processing), the Word application didn't show anything but a non-Doc word screen with no questions being asked. Continuing by clicking the MessageBox OK seemed to then Quit the app and set it to nothing as the VBA code was meant to do. However, when single clicking, or even hovering over, on any Word doc without opening it in Windows Explorer, shows Winword becoming a process in the Windows Task Manager and using up system memory to the tune of about 50 MB. This Winword instance will persist even after you open any other Word document and then exit the Word application using your normal way to open a Word item (double clicking it in Windows explorer) HOWEVER, there was one added benefit I noticed by using your app.visible line of code. Eventually, Winword will pop up the window for the hidden document preventing it from closing and freeing up the memory after you have opened up other Word documents. It came up as a "Document50" blank document in my case and didn't allow you to make any changes to it using the WinWord menus, BUT allowed you to close the application running by clicking on the close application icon (upper right little x) and that then would close the system process Winword instance shown still running in Windows Task Manager. Very perplexing.
- Thread starter
- #5
PROBLEM SOLVED! - The issue actually has to do with having the preview pane on in Windows File Explorer. The preview pane creates a Winword process in Windows Task Manager (uses up about 50 MB of available memory) that persists without showing Windows as open on your taskbar as long as you have an open Windows File Explorer (even minimized) showing a Word document in the preview pane. Sorry for wasting your time.
|C an not replicate the behaviour as you describe it. My bare-bones version of what you describes does exactly what I'd expect it to do, and does not leave any unexpected copies of Word running.
This suggests that perhaps your instantiation code, or addition and removal of documents is not quite as you describe, and is doing something thaty casues the issues you are seeing. I thuink we need toi see your code. Of course, if your main concern is the various versions of Word (or excel) opening up, consider using CreateObject and GetObject rather than New
Code:
[blue] Dim myword As Word.Application
Set myword = New Word.Application
Dim myDoc As Word.Document
Set myDoc = myword.Documents.Open("d:\downloads\deleteme\testing.docx")
myword.Selection.TypeText "eeek"
myDoc.Close
Set myDoc = Nothing
myword.Quit
Set myword = Nothing[/blue]This suggests that perhaps your instantiation code, or addition and removal of documents is not quite as you describe, and is doing something thaty casues the issues you are seeing. I thuink we need toi see your code. Of course, if your main concern is the various versions of Word (or excel) opening up, consider using CreateObject and GetObject rather than New
- Thread starter
- #7
- Status
Similar threads
- Question
- Question
- Locked
- Question