UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Schedule a Macro to Run    
Schedule a Macro to Run

Contents

Running an Excel Macro as a Scheduled Task

It is sometimes useful to run a macro in a workbook as a scheduled task. This article provides a utility that uses one of several methods to do this.

Set Up

To make the utility, copy the code provided into the open workbook event in a spreadsheet. Save the spreadsheet as a macro-enabled template or as a workbook you are willing to copy and paste or download from the link at the end.,

The spreadsheet with this code is the control spreadsheet. The spreadsheet that contains the macro you wish to run is called the target worksheet.

Important: Before opening the control spreadsheet, copy the template and rename the copy to the name you want to use.

When you open the control worksheet for the first time you will see an message box informing you that there is data to fill out.

image:macro01.jpg

When you click OK. You will have control of the spreadsheet. The first thing to do is save the control spreadsheet in the location you plan to keep it.

Enter the following items:

  • Path name to the target spreadsheet
  • File name of the target spreadsheet
  • The name of the macro in the target spreadsheet that is to be run.

Cell B11 should contain the Action for the Task Scheduler. Copy this string to the clipboard and/or notepad. It will come in handy when you schedule the task. Do a web search on how to schedule a task using the task scheduler.

Attached is an example of the filled-in spreadsheet

image:macro02.jpg

Note that the path to the Excel executable varies depending on the version of Excel installed. Change Cell B9 if necessary.

From this point on, the control spreadsheet is automatic.

When you open this spreadsheet it will open the target spreadsheet and run the macro. The results are recorded in the control workbook in Cell B6. To see the results, hold down the SHIFT key while opening the control workbook until the page is fully loaded. You will see one of three possible results.

  • Application ran successfully at <time>
  • File: <filename> was not found in path: <pathname>
  • <Error Number>: <Error Description>
CODE

Option Explicit

Private Sub Workbook_Open()
Dim wb As Workbook
Dim MyPath As String, MyFile As String, MyMacro As String, MacroString As String

On Error GoTo ExitError
MyPath = Range("MyPath")
MyFile = Range("MyFile")
MyMacro = Range("MyMacro")

Sheets(1).Range("B6").ClearContents

If Len(MyPath) = 0 Or Len(MyFile) = 0 Or Len(MyMacro) = 0 Then
   MsgBox "Set path, file and macro, then relaunch program"
   Exit Sub
End If

If Dir(MyPath & "\" & MyFile) = "" Then
   Sheets(1).Range("B6") = "File: " & MyFile & " was not found in path: " & MyPath
   GoTo CloseWorkbook
End If
   
Set wb = Workbooks.Open(MyPath & "\" & MyFile)
MacroString = "'" & MyFile & "'!" & MyMacro
Application.Run MacroString
Workbooks(MyFile).Close savechanges:=True
Sheets(1).Range("B6") = "Application ran successfully at " & Format(Now(), "mm/dd/yyyy hh:nn")
GoTo CloseWorkbook

ExitError:
Sheets(1).Range("B6") = Err & ": " & Error(Err)
Workbooks(MyFile).Close savechanges:=False

CloseWorkbook:
' Close the workbook
ActiveWorkbook.Close savechanges:=True
' Close Excel
Application.DisplayAlerts = False
Excel.Application.Quit

End Sub

Caveats

The task scheduler has some limitations. It is well to know what these are so that you can troubleshoot when a task that does run when kicked off manually, fails when run in the task scheduler.

Environment

The task scheduler does not know about the account login. Environment variables such as paths to executable programs, mapped drives and other programs that might be running are unknown to the task scheduler.

Specify full absolute paths to all executables and files. Do not depend on other programs such as Outlook to be running. Although the application may be running, the task scheduler does not know about it.

So some programs may work when run either from the target workbook or a script with this code, but fail when run from the task scheduler.

Task Interaction

Macros that require input from the user may fail when run from the task scheduler. The process will hang if a dialog box pops up. The task will wait indefinitely for a response.

Putting Application.DisplayAlerts = False in the target executable code will cure some of these problems.

Task Permissions

Macros will not run unless they are enabled. To run the script and the target workbook, both need to be in Trusted Locations defined in Excel’s Trust Center.

Template File

Here is a copy of the control template media:Control_Template.zip

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 7,415 times.  This page was last modified 15:31, 24 October 2016 by dflak.   Disclaimers