Scheduled Execution

Frequently it is necessary to perform repetitive tasks in Excel (such as updating the database or submitting reports). In these cases, scheduled command execution can automate these tasks.

Excel Scheduled Event

You can configure an event in Excel to run a given macro at a set time with the Application.OnTime method:

In VBAProject at add the following code:

Private Sub Workbook_Open()

    Application.OnTime TimeValue("18:30:00"), "Alert"
    'The above code executes the Sub Alert within a module

End Sub

Again in VBAProject add a Module (if one does not exist), and add the following code in it:

Sub Alert()
    MsgBox "See how this macro is punctual"
End Sub

You will need to save the workbook and open it again (having enabled the macros) so that the Workbook_Open subroutine of the example works correctly.

The Workbook_Open code will run as soon as the workbook is opened because it is a code linked to this event.

Note that if the Excel file, which contains the Workbook_Open code, is not open at the scheduled time, the "Alert" macro will not be executed.

Scheduled Event in Windows

So that it is not necessary to leave an Excel file open we can perform the scheduling task with the Windows Task Scheduler ().

For this you need to create a Visual Basic script (.vbs extension) that will be called, for example, via batch file (.bat).

Windows Task Scheduler $\rightarrow$ BatFile.bat $\rightarrow$ VBScript.vbs $\rightarrow$ ExcelWithMacro.xlsm

The names BatFile, VBScript and ExcelWithMacro are examples only and can be customized.

Remember to enable the macros in order to perform a schedule.

1. Create a workbook named ExcelWithMacro.xlsm that has the following macro in Module1:

    Sub windowsScheduler()
        MsgBox "This macro is being executed through the scheduler"
    End Sub

2. Open Windows Notepad () and enter the code below:

    macroVB 'This line will call Sub macroVB

    Sub macroVB()
        Dim path
        Set objExcel = CreateObject("Excel.Application")

        workbookPath = "C:\...\ExcelWithMacro.xlsm" 'Put the path of your Excel workbook

        Set objWorkbook = objExcel.Workbooks.Open(workbookPath,0,FALSE)

        objExcel.Application.Visible = False
        objExcel.Application.Run "'" & workbookPath & "'" & "!Module1.windowsScheduler"

        objExcel.Application.Quit

        Set objExcel = Nothing
    End Sub

3. Save the file with a .vbs extension

4. Again open Notepad and enter the following code and save it as BatFile.bat:

    cscript.exe "C:\...\VBScript.vbs"

Do not forget to replace the path of the VBScript.vbs file in the code above.

Visual Basic is a programming language very similar to VBA (Visual Basic for Aplications), but still they have differences.

Setting Windows Task Scheduler

Done the previous procedures just schedule the task with Windows Task Scheduler:

Open Windows Task Scheduler ()
Select Create Basic Task...
Put a Name for task, select the periodicity, choose _ Start a program_ and put the path of the file BatFile.bat

To test, just wait for the scheduled time.

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

FIRST STEPS

FUNDAMENTALS

RANGE

USERFORM

DEEPENING