Excel VBA
Not yet tested on a real installation — tell us how it went. The code follows the vendor's documentation. Write to support@taskbeacon.io.
For a macro started by Task Scheduler; uses MSXML2.ServerXMLHTTP. Workbook_Open in ThisWorkbook runs the macro and closes Excel afterwards, also after an error, so no Excel window is left waiting.
The code
This is the code the Code tab generates (with start, errors and rows ticked). In the app it already contains your job's own address.
' --- 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 SubHow it works
The code pings /start when the script begins, the plain address with the row count when it ends well, and /fail when it fails, with the error description.
Untick options on the Code tab to leave parts out. See what can be reported.
Run on a schedule
Task Scheduler starts Excel with the workbook; Workbook_Open runs the macro and closes Excel. Create a task with the action Start a program:
- Program: the full path to
EXCEL.EXE(for Microsoft 365 usuallyC:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE). - Arguments: the workbook in quotes, for example
"C:\jobs\report.xlsm".
Excel is a desktop program: choose Run only when user is logged on. Without a signed-in user it may not start or may wait for a dialog nobody sees.
Common problems
- The macro doesn't run. Excel blocks macros in a file that came from the internet or an email (a security banner instead of the macro). Right-click the file → Properties → tick Unblock, or keep it in a Trusted Location (File → Options → Trust Center → Trust Center Settings → Trusted Locations). Company policy can still block it; then ask IT.
- Opening the workbook by hand runs the macro and closes Excel. That is
Workbook_Openat work. To edit the workbook, hold Shift while you open it (File → Open → Browse, hold Shift and click Open; from the Recent list Shift has no effect). - Excel stays open. A dialog is waiting, such as Update links? or a message box in your code. Remove the dialog or answer it in code.
- No ping. The ping waits at most 10 seconds; a proxy or firewall that blocks HTTPS shows up when you run the macro by hand.
Use cases: Monitor an Excel VBA macro run by Task Scheduler. More questions: FAQ.