#1
|
|||
|
|||
How to close all instances of Word and Access applications
Greetings,
I'm a noob and I have a problem. I hope you can help me. I have a few excel macros that open Word, or Access (for instance, to use Word docx as source document for an email) and they work the first time I use them. But when I try to use them again, or if these applications are being opened inside a loop and opening and closing many times, eventually it doesn't work. Task manager shows that word, or Access is open in the background. I've tried to find out how to really, really (I mean it!) close word or access applications from the macro, but nothing seems to kill those beasts. What I try is to set all objects to nothing (ranges, etc), then close the particular .docx, or .accdb that is open, set the object to nothing, then quit the Word, or Access application and set the object to nothing. But it works, say, every other time I use the macro. I've noticed the weirdest thing (to a noob) last night: after the macro runs and stops, if I press the stop button of the VBA editor window, it seems to kill the applications (Word/Access). Here is an example of a minimum code where I find the problem. I've tried opening Access only once in the main procedure, but it seemed to cause more problems, even though it seemed logic to me that I could let Access application open and only open and close the particular .accdb in the second Sub. The same kind of policy with Word also gets me into trouble. Thank you very much for any help you can give me! edit: I'm using Office365 and Windows10. edit2: I always have to work in loops through a list of people I have to send mails to, or a list of .docx I need to open and do stuff in them, or a list of .accdb I have to check. When I say a list, I mean like a list of emails in an excel sheet, or a list of files that are in a folder. Thank you. Code:
Option Explicit Dim fileNomee As String Sub loopingACCESSfilesInFolder() Dim obj As Object Dim mySource As Object Dim file As Object Dim pathh As String pathh = ThisWorkbook.path Set obj = CreateObject("Scripting.FileSystemObject") Set mySource = obj.GetFolder(pathh) For Each file In mySource.Files If file.Name Like "*.accdb" Then fileNomee = file.Name Call ACCESS_make_report End If Next file Set file = Nothing Set mySource = Nothing Set obj = Nothing End Sub Sub ACCESS_make_report() Dim dB As Access.Application Dim qry As QueryDef Dim mytbl As TableDef Dim myfrm As AccessObject Dim rng1 As range Dim sh As Worksheet Dim frm1 As Access.Form Dim frm2 As Access.Form path = ThisWorkbook.path Set sh = Sheets("ACCESS_reports_Stu") Set dB = Access.Application dB.Visible = True dB.OpenCurrentDatabase (path & "\" & fileNomee) 'I do lots of stuff in the database and it all works fine 'when I'm done, I try to close it: dB.CloseCurrentDatabase 'I've tried putting a doevents here, but it didn't help 'DoEvents 'trying to close everything Set qry = Nothing Set rng1 = Nothing Set sh = Nothing Set mytbl = Nothing Set myfrm = Nothing Set frm1 = Nothing Set frm2 = Nothing 'Is this the way to close the application? 'I've tried with acQuitSaveNone and without it. Nothing changes, 'I guess because I've closed the currentDatabase anyway, right? dB.Quit acQuitSaveNone 'again, I've tried a doevents, guessing that means the macro would 'wait untill Access application is really closed before moving on 'but it does nothing to help me 'DoEvents 'the last straw Set dB = Nothing End Sub |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can other applications mess up Copy/Paste in Word VBA script? | diracsbracket | Word VBA | 2 | 12-13-2019 08:04 PM |
Alt+Space doesn't access window's Move/Size/Close system menu | Blaq | Word | 0 | 03-28-2019 12:31 PM |
Highlighting multiple word instances in text | gekser | Word VBA | 9 | 07-10-2014 05:46 AM |
Word 2010 running multiple instances | JBE | Word | 0 | 09-28-2012 06:00 PM |
How many Word applications are running GetObject | stevecarr | Word | 0 | 01-09-2006 07:41 AM |