Remotely Trigger an Excel VBA Macro

The Problem

I’ve recently helped out a client with a project that updated some data and exported a related chart. The client asked if I had a way for him to trigger the VBA procedure (macro) on his PC from his Android phone. He’s not using Office 365 or OneDrive, so he needed a low-tech remote VBA solution.

I decided to base my solution on a synchronized folder in Google Drive or Dropbox, where the presence of a particular file triggers execution of the VBA procedure. It’s easy enough to create a dummy file in one of these shared folders from an Android phone, and even easier if a dummy file exists and all you need to do is rename it.

To run successfully, the remote computer must be running Excel, with the applicable workbook open; Excel’s Application.OnTime function must have started the monitoring process; and a reliable file-sharing service must be synchronizing the target folder automatically.

The Untriggered Project

An oversimplification of the client’s project is shown below. There is some data and a chart that displays the data. The data in C3:C8 is dynamic, in this example because the cells contain the formula =RANDBETWEEN(1,5). The
“Activate Monitoring” button is used to start the monitoring process; I’ll talk about it in a bit.

Why a pie chart? It’s simple enough for a demo; pie charts aren’t quite as evil as we’ve asserted for all these years; and they make easily-distinguishable file icons when viewing a Windows directory, as shown in a screenshot at the end of this article.

Here is the simple VBA routine that updates the data and exports a time-stamped image of the chart:

Sub RecalcAndExportChart()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Sheet1")
  Dim cht As Chart
  Set cht = ws.ChartObjects(1).Chart
  ws.Calculate
  Dim FileName As String
  FileName = FullPath & "Chart_" & Format(Now, "yyyy-mm-dd_hhmmss") & ".png"
  cht.Export FileName
  Debug.Print FileName
End Sub

FullPath calls a function to return the target directory for the exported image of the chart. I use the Debug.Print to tell me if a chart was exported.

The Trigger Mechanism

The Shared Folder

I designed the first version of this remote VBA trigger mechanism to run using Google Drive, and then modified it to run in DropBox. To operate in Google Drive, I first created a top-level folder in my Drive account named _ Remote. I put this folder name into a VBA constant at the top of my code module:

Const REMOTE_DIRECTORY As String = "\Google Drive\_ Remote"

and I referenced this constant in the FullPath function:

Public Function FullPath() As String
  FullPath = Environ("UserProfile") & REMOTE_DIRECTORY & Application.PathSeparator
End Function

Environ is a handy function that returns useful information about the user’s environment. On my computer, Environ("UserProfile") returns the directory of my Windows account:

C:\Users\Jon Peltier

so FullPath returns:

C:\Users\Jon Peltier\Google Drive\_ Remote\

This is where the newly created Google Drive folder is located on my computer. It is set to synchronize automatically, so any changes made on my laptop are reflected (nearly) instantly on my phone, and vice versa.

Trigger Files

I defined three file names (as VBA constants) for various triggers:

Const REMOTE_TRIGGER_FILE_TRUE As String = "true.trigger"
Const REMOTE_TRIGGER_FILE_FALSE As String = "false.trigger"
Const REMOTE_TRIGGER_FILE_STOP As String = "stop.trigger"

Under normal operation, when the PC is waiting to be triggered but I don’t want to trigger it yet, a file named false.trigger will be present in the _ Remote folder. When I want the VBA update and export procedure to run, I change the name of the file to true.trigger. And if I want the PC to stop monitoring for a trigger, I will rename the file stop.trigger.

There is nothing special about these trigger files except their file names. I created false.trigger as a simple text file. More sophisticated versions of this mechanism may use parameters encoded in the contents of the file.

To determine whether a file exists, I use a very old Visual Basic routine I long ago cribbed from a former Visual Basic MVP named Karl Peterson. Pass in the full path and file name, and it returns True or False:

Public Function FileExists(ByVal FileSpec As String) As Boolean
  ' thanks Karl Peterson MS VB MVP
  ' Guard against bad FileSpec by ignoring errors retrieving its attributes
  On Error Resume Next
  Dim Attr As Long
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

I have three functions that tell me whether a trigger file is present:

Public Function IsTriggerSet() As Boolean
  IsTriggerSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_TRUE)
End Function

Public Function IsTriggerNotSet() As Boolean
  IsTriggerNotSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_FALSE)
End Function

Public Function StopTrigger() As Boolean
  StopTrigger = FileExists(FullPath & REMOTE_TRIGGER_FILE_STOP)
End Function

Monitoring and Updating

Finally, I need a VBA procedure to be running on my PC which will detect one of the trigger files and act accordingly:

Sub MonitorAndUpdate()
  If StopTrigger Then
    Debug.Print Now, "Stop"
    If Not IsTriggerNotSet Then
      FileCopy FullPath & REMOTE_TRIGGER_FILE_STOP, FullPath & REMOTE_TRIGGER_FILE_FALSE
    End If
    Kill FullPath & REMOTE_TRIGGER_FILE_STOP
    DeactivateMonitoring
  Else
    If IsTriggerSet Then
      If Not IsTriggerNotSet Then
        FileCopy FullPath & REMOTE_TRIGGER_FILE_TRUE, FullPath & REMOTE_TRIGGER_FILE_FALSE
      End If
      Kill FullPath & REMOTE_TRIGGER_FILE_TRUE
      Debug.Print Now, True
      RecalcAndExportChart
    Else
      Debug.Print Now, False
      If Not IsTriggerNotSet Then
        Dim sFullName As String
        sFullName = FullPath & REMOTE_TRIGGER_FILE_FALSE
        Debug.Print "Creating " & sFullName
        Dim iFile As Long
        iFile = FreeFile
        Open sFullName For Output As iFile
        Close #iFile
      End If
    End If
    ActivateMonitoring
  End If
End Sub

How it works in English is:

• If a file named stop.trigger is present
  • Monitoring stops
  • stop.trigger is renamed to false.trigger
• If a file named true.trigger is present
  • The file is recalculated and the chart is exported
  • true.trigger is renamed to false.trigger
  • Monitoring continues
• If none of the trigger files are present
  • A new false.trigger file is created
  • Monitoring continues
• If a file named false.trigger is present
  • Nothing happens, the program doesn’t even check

Activation and Deactivation of Monitoring

To activate or continue monitoring we need one more VBA constant for MONITOR_DURATION the time between checking for a trigger (here it is set for five seconds):

Const MONITOR_DURATION As String = "0:00:05"

and we need to use Application.OnTime to tell VBA when to run our monitoring procedure MonitorAndUpdate:

Sub ActivateMonitoring()
  If Not IsActive Then
    Debug.Print "Activating"
  End If
  IsActive = True
  NextTime = Now + TimeValue(MONITOR_DURATION)
  Application.OnTime NextTime, "MonitorAndUpdate"
End Sub

IsActive is a variable that keeps track of whether we are just starting our monitoring or whether we have been monitoring all along, and NextTime is another variable that keeps track of the next scheduled monitoring time:

Dim IsActive As Boolean
Dim NextTime As Date

Finally, to abort the monitoring, we need to tell Application.OnTime to cancel the last event we’ve scheduled. If we don’t keep track of NextTime, you can’t cancel the scheduled event.

Sub DeactivateMonitoring()
  On Error Resume Next
  Application.OnTime NextTime, "MonitorAndUpdate", , False
  On Error GoTo 0
  NextTime = 0
  IsActive = False
  Debug.Print "Deactivating"
End Sub

The “Activate Monitoring” button next to the chart in the very first screenshot runs ActivateMonitoring the very first time.

Running the Monitoring Routine

When I started, the _ Remote directory contained only the “Remote.xlsm” workbook that contains the data and chart and the code. I didn’t need to keep the workbook in the _ Remote directory, but it was convenient for this exercise. I hadn’t even bothered to create the empty false.trigger text file.

I started the routine by clicking the “Activate Monitoring” button. This is indicated by “Activating” in the Immediate Window log below.

The first time the program checked for a trigger file was at 10:22:08 AM; “False” indicates that it should not update and export. Since it found no false.trigger file, it created one.

The program checked again five seconds later, at 10:22:13, and took no action, but by 10:22:18, I had renamed the false.trigger file to true.trigger, and we see that a file named Chart_2019-03-07_102218.png was exported.

The monitoring proceeded for several more cycles, exporting three more charts while I tested renaming the trigger file on my phone, in Windows Explorer, and in the web interface for Google Drive.

Finally I renamed the false.trigger file to stop.trigger, and monitoring ceased.

Debug.Print log from Immediate Window
-------------------------------------
Activating
3/7/2019 10:22:08 AM        False
Creating C:\Users\Jon Peltier\Google Drive\_ Remote\false.trigger
3/7/2019 10:22:13 AM        False
3/7/2019 10:22:18 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102218.png
3/7/2019 10:22:23 AM        False
3/7/2019 10:22:28 AM        False
3/7/2019 10:22:33 AM        False
3/7/2019 10:22:38 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102238.png
3/7/2019 10:22:43 AM        False
3/7/2019 10:22:48 AM        False
3/7/2019 10:22:53 AM        False
3/7/2019 10:22:58 AM        False
3/7/2019 10:23:03 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102303.png
3/7/2019 10:23:08 AM        False
3/7/2019 10:23:13 AM        False
3/7/2019 10:23:18 AM        True
C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102318.png
3/7/2019 10:23:23 AM        False
3/7/2019 10:23:28 AM        False
3/7/2019 10:23:33 AM        False
3/7/2019 10:23:38 AM        Stop
Deactivating

When this all finished, I took a screenshot of the Google Drive\_ Remote folder. You can see the four exported chart images, the false.trigger file (which stop.trigger was renamed), and the workbook that runs it all.

Modifying to Work in Dropbox

I had to make only one simple change in the whole routine, to the REMOTE_DIRECTORY constant, for it to work just as well in Dropbox:

Const REMOTE_DIRECTORY As String = "\Dropbox\_ Remote"

Here’s a screenshot of the _ Remote folder on dropbox, taken from my phone.

The synchronization is handled by third-party file sharing applications, and all Excel has to do is wake up periodically to check for a filename, so this routine ought to work in other services besides Google Drive and Dropbox.

The Complete VBA Code

Here is the complete code for the remote VBA triggering project. You can also download my macro-containing workbook in this zip file: Remote.zip.

Option Explicit

Const REMOTE_DIRECTORY As String = "\Google Drive\_ Remote"
'Const REMOTE_DIRECTORY As String = "\Dropbox\_ Remote"
Const REMOTE_TRIGGER_FILE_TRUE As String = "true.trigger"
Const REMOTE_TRIGGER_FILE_FALSE As String = "false.trigger"
Const REMOTE_TRIGGER_FILE_STOP As String = "stop.trigger"
Const MONITOR_DURATION As String = "0:00:10"

Dim IsActive As Boolean
Dim NextTime As Date

Public Function FullPath() As String
  FullPath = Environ("UserProfile") & REMOTE_DIRECTORY & Application.PathSeparator
End Function

Public Function IsTriggerSet() As Boolean
  IsTriggerSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_TRUE)
End Function

Public Function IsTriggerNotSet() As Boolean
  IsTriggerNotSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_FALSE)
End Function

Public Function StopTrigger() As Boolean
  StopTrigger = FileExists(FullPath & REMOTE_TRIGGER_FILE_STOP)
End Function

Public Function FileExists(ByVal FileSpec As String) As Boolean
  ' thanks Karl Peterson MS VB MVP
  ' Guard against bad FileSpec by ignoring errors retrieving its attributes
  On Error Resume Next
  Dim Attr As Long
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

Sub ActivateMonitoring()
  If Not IsActive Then
    Debug.Print "Activating"
  End If
  IsActive = True
  NextTime = Now + TimeValue(MONITOR_DURATION)
  Application.OnTime NextTime, "MonitorAndUpdate"
End Sub

Sub DeactivateMonitoring()
  On Error Resume Next
  Application.OnTime NextTime, "MonitorAndUpdate", , False
  On Error GoTo 0
  NextTime = 0
  IsActive = False
  Debug.Print "Deactivating"
End Sub

Sub MonitorAndUpdate()
  If StopTrigger Then
    Debug.Print Now, "Stop"
    If Not IsTriggerNotSet Then
      FileCopy FullPath & REMOTE_TRIGGER_FILE_STOP, FullPath & REMOTE_TRIGGER_FILE_FALSE
    End If
    Kill FullPath & REMOTE_TRIGGER_FILE_STOP
    DeactivateMonitoring
  Else
    If IsTriggerSet Then
      If Not IsTriggerNotSet Then
        FileCopy FullPath & REMOTE_TRIGGER_FILE_TRUE, FullPath & REMOTE_TRIGGER_FILE_FALSE
      End If
      Kill FullPath & REMOTE_TRIGGER_FILE_TRUE
      Debug.Print Now, True
      RecalcAndExportChart
    Else
      Debug.Print Now, False
      If Not IsTriggerNotSet Then
        Dim iFile As Long
        iFile = FreeFile
        Dim sFullName As String
        sFullName = FullPath & REMOTE_TRIGGER_FILE_FALSE
        Debug.Print "Creating " & sFullName
        Open sFullName For Output As iFile
        Close #iFile
      End If
    End If
    ActivateMonitoring
  End If
End Sub

Sub RecalcAndExportChart()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Sheet1")
  Dim cht As Chart
  Set cht = ws.ChartObjects(1).Chart
  ws.Calculate
  Dim FileName As String
  FileName = FullPath & "Chart_" & Format(Now, "yyyy-mm-dd_hhmmss") & ".png"
  cht.Export FileName
  Debug.Print FileName
End Sub

This code can be pasted into a regular code module, and modified to suit your file sharing situation and the updating and exporting requirements of your project.