Let 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, passing information to the Windows clipboard, and integrating Excel 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.

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).

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.

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
    
    
             Close
    
    
             End If 
    
    
             intTime = Second(Now)
    
    
             Sheets("sheet1").Range("a1").Value = Time 
    
    
             End If 
    
    
             DoEvents 
    
    
            Wend

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.

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

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

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("wscript.shell")
    
    
            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

 Else
    
    
             WScript.Echo strComputerName & " is not running the software!" 
    
    
             End If 
    
    
             End If
    
    
             Loop
    
    
             oFile.Close
    
    
            Else
    
    
             WScript.Echo "The file was not there."
    
    
            End If
    
    
            Set Shell = Nothing
    
    
            Set oFSO = Nothing
    
    
            WScript.Quit

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.

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.

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

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