Monitor If Your VBA Apps Are Running With This Slick Script

Ryan Dube 20-07-2012

monitor application runningLet me paint a scenario for you. Let’s say that you’ve created a scrolling Powerpoint slideshow that you want to display permanently on a large TV display at work. Or, maybe you have an Excel application that includes an entry form for your workers to type information into a database.


There are a lot of reasons why you might want to monitor if an application is running on some remote computer. This may sound like a complicated high-level requirement that you’ll need an IT expert to help you with, but I’m here to tell you that this is a task you can accomplish yourself.

You’ve trusted us to show you how to do other seemingly complicated tasks with VBA before, like sending emails from Excel How to Send Emails From an Excel Spreadsheet Using VBA Scripts Our code template will help you set up automated emails from within Excel using Collaboration Data Objects (CDO) and VBA scripts. Read More , passing information to the Windows clipboard Pass Any Information Between VBA Applications Using The Clipboard One of the most frustrating parts of working with VBA inside specific applications, is that it’s not always easy to get two applications to “talk” to each other. You can try for very quick transactions... Read More , and integrating Excel How to Integrate Excel Data Into a Word Document During your work week, there are probably lots of times that you find yourself copying and pasting information from Excel into Word, or the other way around. This is how people often produce written reports... Read More data into a Word document.

Now, all of those things were relatively passive. Sending emails or passing data from one application to another on a computer is one thing, but in this case we’re going to monitor one application running on one computer, with another application running on a remote computer. It’s possible – trust me.

A Remote Application Monitoring System

This is basically a system that can turn you into a hero at work with just a few simple lines of code on both ends of the setup.

What will the setup look like? Basically, this setup can work with multiple remote computer systems that might be displaying something on a large TV display, or running an Excel data entry screen, or anything else that is an Office application with VBA.


A Handshaking VBA Script

In the example here, I’m going to create one computer where the VBA application writes a file to the local computer hard drive in the c:\temp\handshaking\ directory every 5 seconds. Then, the remote monitoring computer will remotely check that directory every so often for the file. If the file exists, it will delete it (because if the remote program is running, it should just recreate the file again). If the file doesn’t exist, it will issue an alert that the application on that computer is no longer running.

Here’s what that looks like.

monitor application running

First, I’m going to show you how you can add the VBA script on your local Office application that will write the handshaking file every 5 seconds (if the file doesn’t exist already).


Monitor If Your VBA Apps Are Running With This Slick Script handshaking2

In my example, I created two pushbuttons to show you how the script works, but in your application you’ll launch the “Start Handshaking” script when the application starts. In Excel, that’s the Worksheet -> Activate function.

Before you get started with the script that will write the handshaking file, you need to activate the Microsoft Scripting Runtime reference – this allows your VBA app to access Window’s file manipulation tools. You can access this in the VBA editor, clicking on the Tools -> References menu.

monitor application has stopped working


Just scroll down, select Microsoft Scripting Runtime, and you’re good to go.

'On a regular schedule, check if the handshaking file exists.
'If it doesn't, write a new file.
'File is named with the computer ID so monitor knows which
'computer is having a problem.

'Timer script to regularly check c:\scripts\handshake\ for the file.

Dim intTime As Integer
Dim sFile As String
Dim sPath As String
Dim SFname As String
Dim i As Integer

intTime = 5
i = FreeFile
TimerOn = True
sFile = "c:\Temp\MyComputerName.txt"

While TimerOn = True
    If Second(Now) > (intTime + 5) Or Second(Now) = 0 Then

        'Check if file exists
        'If not, recreate it

        If Dir(sFile) = - Then
            'File doesn't exist, recreate it
            MsgBox "created"

            Open sFile For Output As #i
        End If            
        intTime = Second(Now)
        Sheets("sheet1").Range("a1").Value = Time               
    End If     

This script only has 16 lines if you don’t count the variable definitions, that’s a simple script! All it does is sets up a timer loop that runs every 5 seconds. It checks if the handshaking file exists. If it doesn’t, it recreates the file.

If you think about it, this is a simple, creative way to tell the world that the application is alive and running. Let me show you what I mean. When I click the “Start Handshaking” button and then go to the c:\temp\handshaking\ directory, I can see that my VBA script has created the new file. It’s an empty file, but that doesn’t matter – what matters is that the file exists.

monitor application has stopped working


To test my script, I manually delete the text file from the directory.

monitor application has stopped working

A few seconds later, the file re-appears again.

monitor application

That means that at this point, anyone or anything external to your application can check whether or not your program is open by deleting that file and then checking to see if the file reappears. Simple – yet efficient!

Creating a Remote Monitoring Script

Now that you have your VBA handshaking script running on all of your remote applications, you’ll want to create a central program running on some server, or maybe on your own work PC, that goes out and monitors whether those remote apps are running.

The best way to do this is with a script that runs in the background without a GUI front-end. All you want the script to do is give you an alert when any of the remote applications stop running.  The perfect tool for this app is Windows script. Just create a new text file called handshaking.wsf and paste the following script.

Option Explicit

On Error Resume Next

Dim strHost
Dim strComputerName
Dim ReturnCode
Dim strLine
Dim Shell
Dim oFSO, sFile, oFile, sText
Dim oFSO2, sFile2, oFile2, sText2
Dim strFailedList

Set Shell = wscript.createObject("")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFSO2 = CreateObject("Scripting.FileSystemObject")
sFile = "\\MyComputerName\c$\users\owner\scripts\handshaking\terminallist.ini"

strFailedList = ""

If oFSO.FileExists(sFile) Then
  Set oFile = oFSO.OpenTextFile(sFile, 1)
  Do While Not oFile.AtEndOfStream
      sText = oFile.ReadLine
      If Trim(sText) <> - Then
	strComputerName = Trim(sText)
	sFile2 = "\\" & strComputerName & "\c$\users\owner\scripts\handshaking\" & strComputerName & ".txt"
	If oFSO2.FileExists(sFile2) Then
             oFSO2.DeleteFile sFile2, True
	     WScript.Echo strComputerName & " is not running the software!"	    
	End If	
      End If
  WScript.Echo "The file was not there."
End If
Set Shell = Nothing
Set oFSO = Nothing

This script uses Windows File subsystem to first open the “terminallist.ini” file, which basically has all of the remote computer files you’re trying to access. In this case, for my test, it has three lines containing MyComputerName, MyOtherComputer, and YetAnotherComputer.

The script above will go down your list of computers, access the remote machine using the network path (you need to be an administrator, obviously), and if the file exists, it will delete it. If the file doesn’t exist, it will issue an alert.

Running my WSF monitoring script, it didn’t alert on the first computer, because the file was found, but it did alert on the other two because those remote applications don’t exist yet, and aren’t running.

Monitor If Your VBA Apps Are Running With This Slick Script handshaking8

Now, to make sure your monitoring software regularly runs through and checks that all of your remote computers are running your program, you’ll need to set up the WSF script above as a scheduled task that runs every 5 minutes.

Just create a task on your server or your main PC that points to the script.

monitor application

And create an indefinite schedule that runs your monitoring script every 5 minutes.

monitor application running

Now, with just these two simple scripts – one VBA snippet embedded into any of your existing Office programs, and one Windows Script that constantly monitors those remote programs – you will be alerted instantly the moment someone closes that remote program, or the PC shuts off, or the program dies for any reason whatsoever.

If you’re working in a Windows environment and have created a lot of critical applications using Office software, this technique is a very quick and cheap (free!) way to remotely monitor all of those critical apps.

Give it a shot and let me know how it worked out for you? Were you able to impress all of your colleagues with how quickly you knew whenever one of your systems was down? Share your thoughts and experiences in the comments section below.

Image Credits: Young Man Pointing at Computer via Shutterstock

Related topics: Programming, Visual Basic Programming.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Ric
    August 2, 2017 at 2:57 am

    This is a great article, very helpful. Thanks Ryan!

  2. Steven Kim
    July 26, 2012 at 7:25 am

    more of a C# guy but i can try using this script at some point

    • Ryan Dube
      August 9, 2012 at 12:55 pm

      if you know C#, you'll have no problem picking this up quickly.

  3. Luis Gomez
    July 20, 2012 at 10:17 pm

    Great post.


  4. Rigoberto Garcia
    July 20, 2012 at 10:12 pm

    I am currently developing an application using MS Excel with VBA and proceed to implement its monitoring using functionality you describe. Thank you very much Ryan

    • Ryan Dube
      August 9, 2012 at 12:54 pm

      Excellent - I'm glad the article helped.