Monitor an Excel VBA macro run by Task Scheduler
An Excel workbook with a VBA macro is opened by Windows Task Scheduler every morning to refresh a report. Some days it works; some days Excel is still open at noon with a dialog nobody saw, and some days it never started. Each of those three cases needs a different signal, and all three can raise an alert.
Why scheduled Excel macros hang instead of failing
Excel is built for a person sitting in front of it. Microsoft says so directly: it doesn't recommend or support unattended automation of Office, and its 2024 guidance for unattended (RPA) setups explains why. When something unexpected happens, Office shows a dialog and waits for an answer, so the run looks like it hangs. Setting Application.DisplayAlerts = False suppresses many prompts, but not all of them.
That gives three ways a scheduled macro goes wrong, and only one of them is an error:
- An error in the macro: without error handling, VBA shows its debug dialog and Excel stays open. Task Scheduler sees a process that is still running, not a failure.
- A dialog: "Save changes?", links to update, a protected-view bar, a sign-in prompt. The macro waits.
- No start at all: a task set to Run whether user is logged on or not often can't open Excel properly, because there is no desktop to show it on; answers on Microsoft Q&A (2024) recommend Run only when user is logged on, which in turn means nothing runs while you are logged out.
Because the typical failure is a hang, a check that waits for an error never fires. What you need is something that expects the macro to finish and notices when it doesn't, which is also what Microsoft's RPA guidance recommends: monitoring that detects a hung Office process.
Ping from the macro
The code below goes into the workbook. Workbook_Open runs your macro and then closes Excel, also after an error, so no window is left waiting. The macro pings TaskBeacon at the start, sends /fail with the error description if anything fails, and sends the number of used rows on the first sheet when it ends well.
' --- TaskBeacon: put this in a module of the workbook (.xlsm) ---
' Task Scheduler starts Excel with the workbook; Workbook_Open (below) calls RunReport and closes Excel.
Const TB As String = "https://taskbeacon.io/ping/your-job-id"
Private Sub TbPing(ByVal path As String)
On Error Resume Next ' a failed ping must never stop the macro
Dim http As Object
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
http.setTimeouts 2000, 3000, 2500, 2500 ' resolve, connect, send, receive (ms): at most 10 seconds in all
http.Open "GET", TB & path, False
http.send
End Sub
Public Sub RunReport()
TbPing "/start"
On Error GoTo Failed
' --- your code here ---
ThisWorkbook.Worksheets(1).Calculate
Dim tbRows As Long
tbRows = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
TbPing "?rows=" & tbRows
Exit Sub
Failed:
Dim msg As String
msg = Left(Err.Description, 200)
TbPing "/fail?error=" & Application.WorksheetFunction.EncodeURL(msg)
End Sub
' --- TaskBeacon: put this in ThisWorkbook (not in the module) ---
' Runs when the workbook opens, then closes Excel, also after an error, so no Excel stays open.
Private Sub Workbook_Open()
On Error Resume Next ' an error in RunReport must not leave Excel waiting
RunReport
ThisWorkbook.Saved = True ' no "Save changes?" question (call ThisWorkbook.Save in RunReport to keep changes)
Application.Quit
End SubNot yet tested on a real installation — tell us how it went. The code follows the vendor's documentation. Write to support@taskbeacon.io.
In the app, the Code tab of your job gives you this code with the job's own address instead of your-job-id, and you can untick the parts you don't need.
Save the workbook as .xlsm and let Task Scheduler start EXCEL.EXE with the workbook's path as its argument. A failed ping is ignored (On Error Resume Next inside TbPing), so monitoring never stops the report. Because Workbook_Open runs every time the workbook opens, and then closes Excel, keep a separate copy for editing the macro.
What you get
- Late when the macro started but didn't finish in its usual time: the hung-on-a-dialog case.
- Missed when no ping came by the planned time plus grace: Excel never started, or nobody was logged on.
- Failed with the VBA error description, for example a missing source file.
- Odd data when the row count drops more than 40% below the recent runs or is 0.
The Excel VBA page in the Docs has the setup details. For the timing settings that decide when a run counts as Late or Missed, see Grace.
Without TaskBeacon
Add error handling to every macro, set DisplayAlerts = False, and write a line to a log file at the end of a successful run. Then let a second scheduled task check, an hour later, that the log has today's line and that no EXCEL.EXE process is still running, and kill it and send an email if it is. For a report that matters, consider moving the logic to Python or R, which run unattended without dialogs; see Python scheduled script alerts.
Connected Claude or ChatGPT to TaskBeacon? Ask it "what failed last night, and why?" and it answers from your jobs. How that works.
Related
In the Docs: Excel VBA · Statuses · Grace.
- Find out when a scheduled job didn't run at allA run that never happened leaves no error to alert on.
- Task Scheduler Last Run Result 0x0, but the script failedLast Run Result 0x0 only means the program it started returned 0.
- Get an alert when a job loads 0 rows, or far fewer than usualExit code 0 says nothing about how many rows arrived.
TaskBeacon is free to start, with every feature open for now and no credit card. Create a job, paste the code, and the first ping shows up on the job page.
Start free View live demoSources
- Microsoft Learn: Considerations for unattended automation of Office in the Microsoft 365 for unattended RPA environment (2020-03-30, updated 2024-12-18)
- Microsoft Support: Considerations for server-side Automation of Office (undated; checked 2026-11-09)
- Microsoft Q&A: Task Scheduler is not opening an Excel file (2024-10-07)
Page checked against its sources on 2026-11-09. Menus and limits of other products change; if something here no longer matches, write to support@taskbeacon.io.