How to run VBA Macro Daily without opening Excel file autiomtically?

Schedule Excel VBA Macro to Run Daily Automatic

VBA Macro is used to automate lot of repeated tasks in Ms Office applications like Excel, Access, Outlook, Powerpoint, Word etc.,

But, how to run this macro automatically daily or on scheduled time interval without any opening the Excel file that has the macro.

How to run VBA macro without Opening Excel file?

This involves 4 steps. First 3 stes are just preparations.

Last step is the important oen which actually schedules the macro to run daily or on specific date.

1. Excel file VBA macro code.
2. VB script file
3. Test run with cscript
4. Schedule macro in Windows

Here are the steps explained in detail.

1. Excel file with macro (xlsm)

Prepare the Excel file with macro that runs propery withut any compile or run time errors. Also it would be good if it does not wait for any user input like a confirmation message box or input text box.

My sample xlsm file path looks like this:”E:\AutoRun\Test Macro Run from Other Workbook VBS.xlsm”

Public Sub runfromothermacro1()
    ThisWorkbook.Sheets(1).Cells(1, 1) = ThisWorkbook.Sheets(1).Cells(1, 1) + 1
    ThisWorkbook.Sheets(1).Cells(1, 2) = Now
    ThisWorkbook.Save
End Sub
Public Sub runfromothermacro2()
    ThisWorkbook.Sheets(1).Cells(2, 1) = ThisWorkbook.Sheets(1).Cells(1, 1) + 1
    ThisWorkbook.Sheets(1).Cells(2, 2) = Now
    ThisWorkbook.Save
End Sub

This enables the macro to run without any stoppage. Once macro is ready, save the macro enabled Excel file (.xlsm) file in a folder and note down its full path.

If you would like to test this setup, we have the sample file downloads at end of this page.

2. Write VBScript to execute macro within Excel

We cannot schedule a macro present within a Excel file or Access directly. So, we are going to prepare a vb script file that can be scheduled.

Copy the below code, replace file names in the code, with your file names and macro name.

My sample vbs file path looks like this:”E:\AutoRun\MacroScheduler.vbs”

'Create Excel App Instance & Open Xlsm File
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = True
objExcelApp.DisplayAlerts = False

'Define Macro File & Path
sFilePathXlsm = "E:\AutoRun\Test Macro Run from Other Workbook VBS.xlsm"
Set iWb = objExcelApp.Workbooks.Open(sFilePathXlsm)

'1. Run 1st Macro in another Excel
sMacroToRun = "'" & sFilePathXlsm & "'!runfromothermacro1"
objExcelApp.Run sMacroToRun

'2. Run 2nd Macro in same file
sMacroToRun = "'" & sFilePathXlsm & "'!runfromothermacro2"
objExcelApp.Run sMacroToRun

'Save & Close file
iWb.Save
iWb.Close
objExcelApp.DisplayAlerts = True
objExcelApp.Quit

It is almost done. We just need 2 more steps to finalize this automatic schedule.

3. Test run macro with cscript.exe

This is optional step, but it is always good to confirm that vbs file prepared in step 2 does not give any run time error.

To do this check, open command prompt or power shell and try the below command.

• “C:\Windows\System32\cscript.exe” “vbs script file path”

This is to ensure that the schedule macro will run fine.

4. Windows Task Scheduler to make macro run automatically

Here are the steps to schedule the macro to run automatically by using the windows inbuilt application – Task Scheduler.

1. Go to windows taskbar -> Search (or Windows key + S)
2. Type Task Scheduler
3. Click Create New Task and fill details listed.
   • General Tab: Type name for this schedule.
   • Triggers Tab – Click add new & set frequency to run macro (daily, start date end date and time etc)
   • Actions tab: Click add new, type cscript path as in step 3 with double quotes and in “Add arguement” box copy paste vbs script path with double quotes.
4. Click ok.

This completed the Macro autorun daily setup.

You can test this setup by entering current date and time such that the macro gets triggered 5 mins from current time. For example: if current time is 7.00 am, then you can set it to 7.05 am. Wait for 5 mins to see if the VBS script executes the macro within xlsm file correctly.

I hope you liked learning about how to run macro automatically from us. Lets us know if you have any comments.

Download sample setup files:

1. Excel file with Macro: Test Macro Run from Other Workbook VBS.xlsm
2. VB Script: MacroScheduler.vbs

External References:

1. A similar topic that explains how to schedule macro to run daily? – click here
2. Discussion board topic on the same topic – link to the forum