Continuously looping VBScript needs to be more robust to errors

Question

I'm writing a program to open and run multiple VBA modules on a server to upload new data to the cloud. I need to run the modules every 5 minutes at least (preferably every 2 minutes) without stopping 24/7 for an extended period of time. So far, my code will work for 1000-4000 loops, but fails every 1-4 days. How can I make my program more robust?

I'm pretty new to VBScripts, but have pieced together some code that works pretty well from other examples I've found through my research. I've added some error handling (On Error Resume Next and an error check after each major operation) but there are still occasional errors that sneak through and stop the program.

The errors I've been seeing include "unknown VBA runtime error," permission errors (when opening the local log), and server errors. I've tried to mitigate or ignore these with pings and error handling, but some still manage to stop the code from looping.

I've also tried using the Windows Task Scheduler, but that can only go at most every 5 minutes and I couldn't get it to run reliably.

On Error Resume Next

Set WshShell = CreateObject("WScript.Shell")
Dim objFSO
Dim myLog
Dim myLocalLog
Dim pingResultV
Dim pingResultN

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Visible = True
Set myLog = objFSO.OpenTextFile("location of log on server", 8)
Set myLocalLog = objFSO.OpenTextFile("location of log on local hard drive", 8)

Dim i
i = 0
Do While i < 1000000
  'Ping the server to check if the connection is open
  PINGFlagV = Not CBool(WshShell.run("ping -n 1 server",0,True))
  pingResultV = "Null"

  'if the ping was successful, open each file in sequence and run the VBA modules
  If PINGFlagV = True Then
    timeStamp = Now()

    Set Wb = objExcel.WorkBooks.Open("excel file on server",,True)
    objExcel.Application.Run "VBA module to copy data to cloud"
    objExcel.Application.Quit
    If Err.Number <> 0 Then
      myLocalLog.WriteLine(timeStamp & " Error in running VBA Script: " & Err.Description)
      Err.Clear
    End If

    Set Wb2 = objExcel.WorkBooks.Open("second excel file on server",,True)
    objExcel.Application.Run "VBA module to copy this data to cloud"
    objExcel.Application.Quit
    If Err.Number <> 0 Then
      myLocalLog.WriteLine(timeStamp & " Error in running VBA Script 2: " & Err.Description)
      Err.Clear
    End If

    'Write to the server log that it succeeded or failed
    pingResultV = "Server Ping Success"
    myLog.WriteLine(timeStamp & " i=" & i & " --- " & pingResultV)
  Else
    pingResultV = "Server Ping Failed"
    timeStamp = Now()
  End If

  'Write to a local log whether the server was available or not
  myLocalLog.WriteLine(timeStamp & " i=" & i & " --- " & pingResultV)
  If Err.Number <> 0 Then
    myLog.WriteLine(timeStamp & " Error in writing to local log: " & Err.Description)
    Err.Clear
  End If

  'Time between iterations in milliseconds. 120000 ms = 2 minutes
  WScript.Sleep(30000)
  i = i + 1
Loop

I'm not asking for someone to overhaul my code, but I would appreciate any suggestions on improvements. I'm also open to methods other than VBScript if they're more robust.

Some additional background on the system that might be helpful: I have 2 computers that upload data to a server every few minutes, but these computers can't be connected to the internet for security reasons. I need to upload the data these computers generate to an internet database every few minutes so it can be accessed remotely. The code I wrote runs on a separate computer that's connected to the server and internet, and it periodically opens the excel files on the server in read-only mode and runs a VBA script that uploads new data lines to the cloud.

Answer 1

It's hard to tell whether your VBScript or your VBA module or both fail. From your error descriptions, I somewhat expect the latter to be the case. So I'd also suggest to check your VBA coda (again). As for this script, one thing I'd suggest to get rid off the permission / log file error would be to move the logging to its own method, e.g.

' *** VBScript (FSO) ***
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Log file
Const LOG_FILE = "C:\MyData\MyLog.txt"

Sub WriteLog (ByVal sLogFile, ByVal sText)
    Dim oFso
    Dim oLogFile

    Set oFso = CreateObject("Scripting.FileSystemObject")
    Set oLogFile = oFso.OpenTextFile(sLogFile, ForAppending, True)

    ' Prefix log message with current date/time
    sText = Now & " " & sText
    oLogFile.WriteLine sText
    oLogFile.Close

    ' Free resources
    Set oLogFile = Nothing
    Set oFso = Nothing

End Sub

And call it like

If Err.Number <> 0 Then
    WriteLog LOG_FILE, "Error in writing to local log: " & CStr(Err.Number) & ", " & Err.Description
    Err.Clear
End If

I also suggest to release all resources at the end of the loop, e.g. the Excel object, for which you need to move the object creation into the loop:

Dim i
i = 0
Do While i < 1000000

    ' Start of loop
    Set objExcel = CreateObject("Excel.Application")
    objExcel.DisplayAlerts = False
    objExcel.Visible = True

    ' Do your stuff ...

    ' End of Loop -> free resources
    Set Wb = Nothing
    Set Wb2 = Nothing
    objExcel.WorkBooks.Close
    Set objExcel = Nothing

    ' Time between iterations in milliseconds. 120000 ms = 2 minutes
    WScript.Sleep(30000)
    i = i + 1
Loop

And as I'm not an Excel expert, I wonder if objExcel.Visible = True does make sense here or if it would better be set to False?