none
VBA Close Excel Problem

    Question

  • I am using window 7, Excel 2010, Access 2010.  I have a routine (running in Access 2010) that opens an excel file, reads in the data, then closes the excel file.

    Everything work fine except for closing the excel file.  If I watch Windows Task Manager EXCEL.EXE does not terminate.

    Below is a snipit of code that duplicates the problem I am seeing.  How can I close and terminated the EXCEL? 

    The routine is called multiple times.  The first time through the process EXCEL does not close.   The second time a second copy of EXCEL is created and at the end of the routine it does close, but the first occurance is still there.  If I press the VBA reset (stop) button excel will then terminate.

    Function TestOpenCloseExcel(sExcelFile As String) As Integer
                   
      Dim ExcelWorkBook  As Workbook
      Dim iResult As Integer
      Dim ExcelApp As Object
     
      On Error GoTo ErrorTrap

      Debug.Print "Open"
      Set ExcelApp = CreateObject("Excel.Application")
      Set ExcelWorkBook = ExcelApp.Workbooks.Open(sExcelFile)

      Debug.Print "Close"
     
      ExcelWorkBook.Close
      Set ExcelWorkBook = Nothing
      ExcelApp.Quit 'this line terminates the process
      Set ExcelApp = Nothing
     
      Exit Function

    ErrorTrap:
      Debug.Print "(" & Err.number & ") " & Err.Description
    End Function

    Thursday, January 27, 2011 7:25 PM

Answers

  • mathew,

    The problem most likely lies elsewhere.

    Look for *anywhere* in your app where you use any Excel objects or methods in conjunction with a WITH-block of code.

    It is a commonly known issue that Unless your code explicitly declares and clears automation-based object variables used as the object of a with-block (With...End With syntax), those objects can get stuck in memory as "orhpaned" com objects. This will then prevent the release of their automation server classes (since a consitiuent object still has a non-zero COM reference count). This will present as the symptom you are observing: Excel won't close despite the call to .Quit.

    Automation-based  object instances should ALL be used like this:

    Dim objTemp as (Object/Excel.range/whatever)

    set objTemp = (whatever)
    With ObjTemp
      'do stuff here
    End With
    set objTemp = Nothing

    what you should NOT be doing is this:

    With objExclApp.worksheets(1).range("myNamedrange")
      'do stuff
    End With

    THIS is the sort of thing that can leave phantom references for a range object (if not also a worksheet object) stuck in memory because they don't get cleaned up properly.

    Examine the rest of your code for anything that fits this pattern and explicitly declare and clear appropriate variables for those object references. This may well eliminate your issue.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    • Marked as answer by Bessie Zhao Tuesday, February 08, 2011 9:14 AM
    Thursday, January 27, 2011 10:24 PM
  • As I understand this the piece of code I've provided here http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/f107ba63-2895-4590-872d-4c37bdd8111c

    As I said before I had tested it and hadn't faced this error. Just for my interest I've modified this func a little to run it more than once. I opened Task Manager and was watching on EXCEL.EXE process. It opens and closes properly within every iteration of the loop and there is no such a behaviour you are describing above.

    Maybe this function in your full code is being called from another function/sub and the real reason is there? Try to terminate all EXCEL.EXE before testing and then run exactly this function 3-5 times. If there is no problem look at the another place in your code which can generate this issue.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bessie Zhao Tuesday, February 08, 2011 9:14 AM
    Friday, January 28, 2011 7:09 AM
  • CNC_Matthew:

    In the code you have posted, you have not done the following, but I wonder whether, in the code you are actually running:

    1. you have declared the variable, ExcelApp, in the General Declarations Section (at the top) of your module; and
    2. you are not setting ExcelApp to Nothing (at least between successive calls to the function).

    The above would account for the behaviour you are experiencing.

    In the following demo, notice that it is only when the variable ExcelApp is set to Nothing or when the ExcelApp variable goes out of scope that the Excel process finally disappears from the Windows Task Manager.  The Excel process does not disappear from Task Manager when you execute Excel's Quit command.

     

     

    Private ExcelApp As Excel.Application
    
    
    Function TestOpenCloseExcel(sExcelFile As String) As Integer
        
     Dim ExcelWorkBook As Excel.Workbook
     Dim iResult As Integer
     
     On Error GoTo Error_TestOpenCloseExcel
    
     Debug.Print "Start Excel"
     Set ExcelApp = CreateObject("Excel.Application")
     
     ' Just so you can see when the Excel icon
     ' disappears from the Taskbar:
     ExcelApp.Visible = True
    
     Debug.Print "Open Workbook"
     Set ExcelWorkBook = ExcelApp.Workbooks.Open(sExcelFile)
     
     Debug.Print "Close Workbook"
     ExcelWorkBook.Close
     Set ExcelWorkBook = Nothing
     
     
     ' > "this line terminates the process"
     ' Actually, no - it doesn't.
     '
     ' The following code line causes Excel to disappear
     ' from the Taskbar, but Excel remains in the list
     ' of processes in Windows Task Manager:
     ExcelApp.Quit
     
     
     ' The following code line causes the
     ' Excel process to disappear from Task Manager.
     Set ExcelApp = Nothing
     
     ' NOTE:
     '
     ' If the above code line is omitted, the Excel
     ' process won't disappear from the Windows Task
     ' Manager until the ExcelApp variable goes out of
     ' scope (when this function terminates if the
     ' variable is declared within this function).
     '
     ' HOWEVER, if the above code line is omitted
     ' and the ExcelApp variable is declared in the
     ' General Declarations Section (at the top of
     ' the module), then the Excel process won't
     ' disappear from the Windows Task Manager.
     ' Perhaps that's what you're doing.
    
    
     ' Initialize this function's return value:
     iResult = 1
    
    Exit_TestOpenCloseExcel:
    
     ' Set this function's return value:
     TestOpenCloseExcel = iResult
     Exit Function
     
    Error_TestOpenCloseExcel:
    
     Debug.Print "(" & Err.Number & ") " & Err.Description
     Resume Exit_TestOpenCloseExcel
     
    End Function
    
    • Marked as answer by Bessie Zhao Tuesday, February 08, 2011 9:14 AM
    Sunday, January 30, 2011 6:14 PM

All replies

  • "CNC_Matthew" wrote in message
    news:908ba72a-3293-4eb9-b80e-fd2e6e78e185@communitybridge.codeplex.com...
    >I am using window 7, Excel 2010, Access 2010.  I have a routine (running in
    >Access 2010) that opens an excel file, reads in the data, then closes the
    >excel file.
    >
    > Everything work fine except for closing the excel file.  If I watch
    > Windows Task Manager EXCEL.EXE does not terminate.
    >
    > Below is a snipit of code that duplicates the problem I am seeing.  How
    > can I close and terminated the EXCEL?
    >
    > The routine is called multiple times.  The first time through the process
    > EXCEL does not close.   The second time a second copy of EXCEL is created
    > and at the end of the routine it does close, but the first occurance is
    > still there.  If I press the VBA reset (stop) button excel will then
    > terminate.
    >
    > Function TestOpenCloseExcel(sExcelFile As String) As Integer
    >
    >  Dim ExcelWorkBook  As Workbook
    >  Dim iResult As Integer
    >  Dim ExcelApp As Object
    >
    >  On Error GoTo ErrorTrap
    >
    >  Debug.Print "Open"
    >  Set ExcelApp = CreateObject("Excel.Application")
    >  Set ExcelWorkBook = ExcelApp.Workbooks.Open(sExcelFile)
    >
    >  Debug.Print "Close"
    >
    >  ExcelWorkBook.Close
    >  Set ExcelWorkBook = Nothing
    >  ExcelApp.Quit 'this line terminates the process
    >  Set ExcelApp = Nothing
    >
    >  Exit Function
    >
    > ErrorTrap:
    >  Debug.Print "(" & Err.number & ") " & Err.Description
    > End Function
    >
     
    I can't reproduce the problem with Access/Excel 2003 using the above code.
    Can you confirm that the simplified function you posted does cause it for
    you?
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Thursday, January 27, 2011 9:09 PM
  • "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message
    news:7298d953-50fd-4913-a6e8-4a872f4f9f63@communitybridge.codeplex.com...
    >
    > I can't reproduce the problem with Access/Excel 2003 using the above code.
    > Can you confirm that the simplified function you posted does cause it for
    > you?
     
    >
     
    I also don't reproduce it using Access 2010 with Excel 2003.  I don't have
    Excel 2010 installed to test with.  But I wonder if you have cut out the
    part of the code that caused the problem.  Please confirm that the code as
    posted does cause the problem for you.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Thursday, January 27, 2011 9:13 PM
  • CNC_Matthew, 

    I've tested your code which you posted and I didn't have any issue. I've opened excel file and close, and release excel process were successfully.  

    Thursday, January 27, 2011 10:21 PM
  • mathew,

    The problem most likely lies elsewhere.

    Look for *anywhere* in your app where you use any Excel objects or methods in conjunction with a WITH-block of code.

    It is a commonly known issue that Unless your code explicitly declares and clears automation-based object variables used as the object of a with-block (With...End With syntax), those objects can get stuck in memory as "orhpaned" com objects. This will then prevent the release of their automation server classes (since a consitiuent object still has a non-zero COM reference count). This will present as the symptom you are observing: Excel won't close despite the call to .Quit.

    Automation-based  object instances should ALL be used like this:

    Dim objTemp as (Object/Excel.range/whatever)

    set objTemp = (whatever)
    With ObjTemp
      'do stuff here
    End With
    set objTemp = Nothing

    what you should NOT be doing is this:

    With objExclApp.worksheets(1).range("myNamedrange")
      'do stuff
    End With

    THIS is the sort of thing that can leave phantom references for a range object (if not also a worksheet object) stuck in memory because they don't get cleaned up properly.

    Examine the rest of your code for anything that fits this pattern and explicitly declare and clear appropriate variables for those object references. This may well eliminate your issue.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    • Marked as answer by Bessie Zhao Tuesday, February 08, 2011 9:14 AM
    Thursday, January 27, 2011 10:24 PM
  • As I understand this the piece of code I've provided here http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/f107ba63-2895-4590-872d-4c37bdd8111c

    As I said before I had tested it and hadn't faced this error. Just for my interest I've modified this func a little to run it more than once. I opened Task Manager and was watching on EXCEL.EXE process. It opens and closes properly within every iteration of the loop and there is no such a behaviour you are describing above.

    Maybe this function in your full code is being called from another function/sub and the real reason is there? Try to terminate all EXCEL.EXE before testing and then run exactly this function 3-5 times. If there is no problem look at the another place in your code which can generate this issue.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bessie Zhao Tuesday, February 08, 2011 9:14 AM
    Friday, January 28, 2011 7:09 AM
  • CNC_Matthew:

    In the code you have posted, you have not done the following, but I wonder whether, in the code you are actually running:

    1. you have declared the variable, ExcelApp, in the General Declarations Section (at the top) of your module; and
    2. you are not setting ExcelApp to Nothing (at least between successive calls to the function).

    The above would account for the behaviour you are experiencing.

    In the following demo, notice that it is only when the variable ExcelApp is set to Nothing or when the ExcelApp variable goes out of scope that the Excel process finally disappears from the Windows Task Manager.  The Excel process does not disappear from Task Manager when you execute Excel's Quit command.

     

     

    Private ExcelApp As Excel.Application
    
    
    Function TestOpenCloseExcel(sExcelFile As String) As Integer
        
     Dim ExcelWorkBook As Excel.Workbook
     Dim iResult As Integer
     
     On Error GoTo Error_TestOpenCloseExcel
    
     Debug.Print "Start Excel"
     Set ExcelApp = CreateObject("Excel.Application")
     
     ' Just so you can see when the Excel icon
     ' disappears from the Taskbar:
     ExcelApp.Visible = True
    
     Debug.Print "Open Workbook"
     Set ExcelWorkBook = ExcelApp.Workbooks.Open(sExcelFile)
     
     Debug.Print "Close Workbook"
     ExcelWorkBook.Close
     Set ExcelWorkBook = Nothing
     
     
     ' > "this line terminates the process"
     ' Actually, no - it doesn't.
     '
     ' The following code line causes Excel to disappear
     ' from the Taskbar, but Excel remains in the list
     ' of processes in Windows Task Manager:
     ExcelApp.Quit
     
     
     ' The following code line causes the
     ' Excel process to disappear from Task Manager.
     Set ExcelApp = Nothing
     
     ' NOTE:
     '
     ' If the above code line is omitted, the Excel
     ' process won't disappear from the Windows Task
     ' Manager until the ExcelApp variable goes out of
     ' scope (when this function terminates if the
     ' variable is declared within this function).
     '
     ' HOWEVER, if the above code line is omitted
     ' and the ExcelApp variable is declared in the
     ' General Declarations Section (at the top of
     ' the module), then the Excel process won't
     ' disappear from the Windows Task Manager.
     ' Perhaps that's what you're doing.
    
    
     ' Initialize this function's return value:
     iResult = 1
    
    Exit_TestOpenCloseExcel:
    
     ' Set this function's return value:
     TestOpenCloseExcel = iResult
     Exit Function
     
    Error_TestOpenCloseExcel:
    
     Debug.Print "(" & Err.Number & ") " & Err.Description
     Resume Exit_TestOpenCloseExcel
     
    End Function
    
    • Marked as answer by Bessie Zhao Tuesday, February 08, 2011 9:14 AM
    Sunday, January 30, 2011 6:14 PM
  • i'm getting the same problem even with a very simple code

    the code was written in word VBA to go to certain Cell in excel and add one to it then copy back the new value to word

    the program run for 1st time then stop till i go to end excel process in task manager

    i tried even to add the "KILL" function but nothing seems to be work

    ============================================================

    Private Sub CommandButton1_Click()

    Dim objExcel As New Excel.Application

    Dim exWb As Excel.Workbook
     
    Set exWb = objExcel.Workbooks.Open("c:\temp\expenses.xlsx")

    Sheets("sheet1").Cells(4, 2).Value = Sheets("sheet1").Cells(4, 2).Value + 1

    ThisDocument.yrTotal.Caption = exWb.Sheets("Sheet1").Cells(4, 2)

    exWb.Save

    exWb.Close

    objExcel.Application.Quit

    Set exWb = Nothing

    Set objExcel = Nothing
     
    End Sub

     
    Friday, May 31, 2013 1:22 PM
  • i'm getting the same problem even with a very simple code

    the code was written in word VBA to go to certain Cell in excel and add one to it then copy back the new value to word

    the program run for 1st time then stop till i go to end excel process in task manager

    i tried even to add the "KILL" function but nothing seems to be work

    ============================================================

    Private Sub CommandButton1_Click()

    Dim objExcel As New Excel.Application

    Dim exWb As Excel.Workbook
     
    Set exWb = objExcel.Workbooks.Open("c:\temp\expenses.xlsx")

    Sheets("sheet1").Cells(4, 2).Value = Sheets("sheet1").Cells(4, 2).Value + 1

    ThisDocument.yrTotal.Caption = exWb.Sheets("Sheet1").Cells(4, 2)

    exWb.Save

    exWb.Close

    objExcel.Application.Quit

    Set exWb = Nothing

    Set objExcel = Nothing
     
    End Sub

     

    The reason -- at least, one reason -- is obvious: you don't always qualify the keyword "Sheets". Instead of this:

        Sheets("sheet1").Cells(4, 2).Value = Sheets("sheet1").Cells(4, 2).Value + 1

    write this:

        exWb.Sheets("sheet1").Cells(4, 2).Value = exWb.Sheets("sheet1").Cells(4, 2).Value + 1

    and see if that fixes it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Fernando Rivas Wednesday, July 30, 2014 9:53 PM
    Friday, May 31, 2013 1:47 PM
  • Wwoow, it works as a charm

    thank you so much

    i thought as long as i already decleared my work book, so no need to qualify the sheet again

    thanks you again for saving me with this simple solution.

    • Proposed as answer by HotTrigger Friday, May 31, 2013 6:33 PM
    Friday, May 31, 2013 2:08 PM
  • Did you click the wrong post?  You seem to have proposed your post as the answer, but if my post solved your problem, I would think you wouild mark that post as "the answer".


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Friday, May 31, 2013 6:37 PM
  • Dirk,

    For the past few days, I have been encountering a problem such as the one described above but it is in Access 2013. I have an Access desktop database that is being started several times per hour via a command line batch program using Task Scheduler.

    When this database is started it executes a Autoexec macro that performs several simple queries that inputs some text data strings, and uses these text strings to updates/append summary databases.

    After this is done, the Access database is suppose to EXIT via a QuitAccess macro with the Exit Option selected. (I have also tested this QuiteAccess macro with both the Prompt and Save All Options but none of these three options appear to work.)

    Everything works absolutely perfect with this Access 2013 applications with the exception that each time it runs it creates an Background Orphan Process that continues to run after the Access database has exited. As I previously mentioned, this Access application is being run several times per hour and each time it runs it create an orphan background process. The only way to get rid of these orphan processes, is to end them individually in the Task Manager applet, or reboot the computer on which this application is running. 

    This Access application is still being developed and tested, but is slated to go into production as soon as this final problem is resolved.

    BTW: I have attempted to solve this problem using the Command Line "Taskkill" and while this command deletes all orphan processes, it "appears" to create other problems the next time the Access database is started.

    Is there any known reason why the QuitAccess macro does not work? I have also tried using the CloseDatabase macro before using the QuiteAccess and it still does not work.

    Any suggestions regarding how to solve this problem will be most appreciated.

    James Barnes

     

    Monday, October 07, 2013 2:56 PM
  • Just to clarify, James, is the "orphan" background process an instance of Excel, as in the cases discussed in this thread, or is it Access?

    I think solving the problem is likely to require knowing more about exactly how your database is updating the summary databases you refer to.  Could you post the contents of the AutoExec macro in question and any other macros involved, and also any VBA procedures that are executed?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, October 07, 2013 3:21 PM
    • Dirk,

    Thanks for the prompt reply.

    Sorry that I was not clear about the type orphan processes being spawned, they are MS Access processes.

    It is possible that I am on the wrong forum but I saw that you are a MS Access MVP, and that is why I posted to this forum.  ( I am not using Excel.)

    The Access database that I am developing is very simple, and has only two different type tables.

    • The first tables is composed of transactional records that are built from text data streams imported via Import External Data function. These text streams are less than one hundred bytes in length, and include a space as the field delimiter.
    • The second table is a summary table that includes all records that were imported into the first table. This fields in this table are formatted the same as those in the first table, and is updated using a simple Append Query. 

    The Microsoft Access 2013 application is started via a command line batch job that is triggered by the Windows 8 Task Scheduler. This batch job is started several times per hour and the only thing that it does is to start Access. The Access applications has a single AutoExec macro that runs three additional macros:

    1. The first macro runs a query that imports the external text data stream and creates the transactional table.
    2. The second macro runs a query that appends the records in the transactional table to a summary database that includes all transactional in chronological order.
    3. The third macro simply a QuitAccess macro that is suppose to close Access until the next time that it is activated by the Task Scheduler.

    Unfortunately, when Access "quits", it spawns an orphaned Access Background, Process that must be manually deleted.  As you can imagine, this Access job run several times per hour, and the end result is that in a short period of time, hundreds of orphan processes can be running. This will eventually use all of the available resources of the computer, and it will shutdown.

    (The only VBA code running in this application is a simple DeleteTable function that deletes the transactional table before importing new text records.)

    This application is currently running on a test computer but as soon as this last problem is solved it will be moved to a production system.

    Again, thank for your help, and any additional assistance that you can provide will be appreciated.

    James

    Monday, October 07, 2013 7:33 PM
  • Hmm, nothing in your description *sounds* like it ought to leave Access hanging.  Are you able to activate one of these orphan Access processes and make sure that it isn't displaying an error dialog or a save prompt?

    If you open an instance of this database youself, manually, does the same thing happen?  In that case, can you see on the screen a dialog or save prompt that would explain it?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, October 07, 2013 7:57 PM
  • Unfortunately, when Access "quits", it spawns an orphaned Access Background, Process that must be manually deleted.  As you can imagine, this Access job run several times per hour, and the end result is that in a short period of time, hundreds of orphan processes can be running. This will eventually use all of the available resources of the computer, and it will shutdown.

    Hi James,

    This situation of an orphaned Access I have met when I was running some loop, and within this loop an Access application was opened.

    I solved it by having to loop to run a Sub (or Function) in which the Access application was opened.

    You can recognize this?

    Imb.

    Monday, October 07, 2013 8:55 PM
  • James,

    Have you considered getting Access to run your macros every 15 minutes?  Your database will not then have to be opened and closed every 15 minutes.

    I am assuming you want to use macros, rather than Visual Basic for Applications (VBA).  However, the following solutions use a little VBA, to call your macros at regular intervals.

    I am using Access 2010.

    Try one of the following solutions on a COPY of your database.

    You will know how to do much of what is described below. The full description is there for clarity of all readers.

     

    SOLUTION 1

    RUN MACROS AT DATABASE STARTUP AND EVERY 15 MINUTES THEREAFTER

     

    1.   Create a new blank form and save it.

    2.   Make the form open automatically when the database opens (File > Options > Current Database > Display Form > Select the name of your form).

    3.   Open the form in design view.  (In the Navigation pane, right-click the form and select Design View.)

    4.   Open the properties sheet for the form (by double-clicking the black square at the intersection of the horizontal and vertical rulers in top left corner).

    5.   In the properties sheet, click the Event tab.

    6.   Click in the On Open property.

    7.   Click the down-arrow on the right and select Event Procedure.

    8.   Click in the Timer Interval property and enter 900000.  There are 900000 milliseconds in 15 minutes.

    9.   Click in the On Timer property.

    10.   Click the down-arrow on the right and select Event Procedure.

    11.   Click the ellipsis (…) on the right to open the VBA editor.

    12.   Delete all code in the VBA window that opens and replace it with the following (which you can copy and paste):

    Option Compare Database
    Option Explicit
    
    '   Declare the True/False (Boolean) variable,
    '   mfIsRunning:
    Private mfIsRunning As Boolean
    
    
    Private Sub Form_Open(Cancel As Integer)
        
        '   This is the form open event procedure.
        '   It runs when the form opens.
        
        '   Call the RunMacros subprocedure (below):
        Call RunMacros
    
    End Sub
    
    Private Sub Form_Timer()
        
        '   This is the form timer event procedure.
        '   It runs whenever the timer interval expires.
        
        '   The timer interval property of the form
        '   is set to 900000 (milliseconds) so this
        '   code runs every 15 minutes.
        
        '   Call the RunMacros subprocedure (below):
        Call RunMacros
    
    End Sub
    
    Private Sub RunMacros()
    
        '   Do not run the macros if this
        '   subprocedure didn't finish the
        '   last time it was called:
        If mfIsRunning = True Then
            Exit Sub
        End If
    
        '   Set variable to TRUE
        '   while the macros are running:
        mfIsRunning = True
        
        '   Run the macros
        '   (change to your macro names):
        DoCmd.RunMacro "Macro1"
        DoCmd.RunMacro "Macro2"
        '   Don't run your Macro3,
        '   which closes the database.
        
        '   Set variable to FALSE
        '   as macros have finished running:
        mfIsRunning = False
        
    End Sub
    

    13.   The above code assumes your macros are named Macro1 and Macro2.  Change the names in the code to your macro names but do not use the name AutoExec.  Call your first two macros.  Don't call your third macro because you do not want the database to quit.  Keep the database and the form open for as long as you want the macros to run.  You will need to check from time-to-time to see if the database is happy to run indefinitely, e.g. memory issues?

    14.   In the VBA editor, click Debug > Compile Database.  You should encounter no compile errors.

    15.   In the VBA editor, click File > Save to save the VBA code.

    16.   On the Windows Taskbar (at the bottom of the screen), click Access to return to the form in design view. 

    To switch back to the VBA editor, you can use the VBA editor icon on the Windows Taskbar, or, when the form is in Design View, click the View Code button on the Design ribbon under Form Design Tools (on the ribbon), or click an ellipsis in an event property on the form's property sheet as already described.

    17.   Close and save the form

    18.   Open the form.  (In the Navigation Pane, right-click the form and select Open.)

    19.   Your macros will now run (when the form opens and every 15 minutes thereafter until the form is closed).

     

    SOLUTION 2

    RUN MACROS EVERY 15 MINUTES DURING SPECIFIC TIMES OF THE DAY

     

    1.   Implement solution 1 above with the following change.

    2.   At step 12, copy and paste the following code into the VBA module (that lies behind the form):

     

    Option Compare Database
    Option Explicit
    
    '   Declare the True/False (Boolean) variable,
    '   mfIsRunning:
    Private mfIsRunning As Boolean
    
    
    '   The macros must only run during specific
    '   times, so declare variables to store
    '   the start and end times. (Date variables
    '   store dates AND times.)
    Private mdStartTime As Date
    Private mdEndTime As Date
    
    
    Private Sub Form_Open(Cancel As Integer)
       
        '   This is the form open event procedure.
        '   It runs when the form opens.
        
        '   START TIME
        '   Initialize the start time to 7:45 am.
        '   The macros will then begin running
        '   at 8 am, 15 minutes later, because
        '   the Timer Inverval has been set to
        '   900000 (milliseconds).
        mdStartTime = TimeSerial(7, 45, 0)
        
        '   END TIME
        '   Initialize the end time to 6.30 pm
        '   (when the macros will stop running):
        mdEndTime = TimeSerial(18, 30, 0)
        
    End Sub
    
    Private Sub Form_Timer()
        
        '   This is the form timer event procedure.
        '   It runs each time the timer interval expires.
        Call RunMacros
    
    End Sub
    
    Private Sub RunMacros()
    
        '   Do not run the macros if this
        '   subprocedure didn't finish the
        '   last time it ran:
        If mfIsRunning = True Then
            Exit Sub
        End If
    
        '   See if current time of day is between
        '   the start and stop times:
        If Time() >= mdStartTime And Time() <= mdEndTime Then
    
            '   Set variable to TRUE
            '   while the macros are running:
            mfIsRunning = True
            
            '   Run the macros
            '   (change to your macro names):
            DoCmd.RunMacro "Macro1"
            DoCmd.RunMacro "Macro2"
            '   Don't run your Macro3,
            '   which closes the database.
            
            '   Set variable to FALSE
            '   as macros have finished running:
            mfIsRunning = False
        
        End If
        
    End Sub
    

    FINAL COMMENTS

    If either of these solutions works for your application, you may want to develop the form further.  For example, you may want to include the following:

    1.   A label on the form displaying the message "This form's Timer Event is running macros.  Please do not close this form unless authorized."  (Use SHIFT-ENTER to separate lines.)

    2.   A command button, which closes the form.

    3.   Set the following properties on the form's property sheet:

    On the Format tab:

         Border Style = Dialog

         Record Selectors = No

         Navigation Buttons = No

         Dividing Lines = No

    On the Other tab:

         Modal = Yes

         Pop-up = Yes

    Geoff



    • Edited by Geoff_G Friday, October 11, 2013 11:40 AM
    Friday, October 11, 2013 10:58 AM