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.
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.
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:
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.
Done the previous procedures just schedule the task with Windows Task Scheduler:
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.
© 2023 SuperExcelVBA | ABOUT