Logging Errors to a Text File

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The Scripting Runtime object library simplifies the code required to read from and write to a text file. To use the new objects to write to a text file, you return a file object that refers to a new or existing file, and then use the OpenAsTextStream method to open it for input or output. The OpenAsTextStream method has an IOMode argument, which you can set to indicate whether you want to read from the file, write to it, or append to it.

The OpenAsTextStream method returns a TextStream object, which is the object you use to work with the text in the file. To read a line, for example, you can use the TextStream object's ReadLine method; to write a line, you can use the WriteLine method. When you're finished working with the file, you can use the Close method to close it.

The following procedure logs an error to a text file. It takes two arguments: an ErrObject argument, which is a reference to the Err object that contains the current error, and an optional strProcName argument, which specifies the procedure in which the error occurred.

The LogError procedure writes to a text file in the Microsoft® Windows® Temp folder. To determine where the Windows Temp folder is, it calls another procedure, the GetTempDir procedure. This procedure makes a call to the Windows application programming interface (API) to determine the Temp folder. Windows cannot boot without a designated Temp folder, so you can be certain that the Temp folder will always be available.

The LogError procedure is meant to be used to log multiple errors. The first time the procedure is called, no log file exists, so it must create one. On each subsequent call, the procedure must open the existing log file. The simplest way to do this is to look for the name of the file that you're expecting, and if it is not there, handle the error and create the file.

Unfortunately, when the procedure is first called and the error occurs, the existing information in the Err object is cleared and the information for the new error takes its place. Because there is only one Err object available in Microsoft® Visual Basic® for Applications (VBA), the error information that you passed to the procedure is lost when a new error occurs. Therefore, the first thing that the procedure does is to store the error number and description of the error in variables.

When the procedure has a reference to the text file (APP_ERROR_LOG), it opens it for appending, and then writes the error information to the file line by line.

Sub LogError(errX As ErrObject, _
             Optional strProcName As String)
              
   ' This procedure logs errors to a text file. It is used in
   ' this section to log synchronization errors.
   '
   ' Arguments:
   ' errX: A variable that refers to the VBA Err object.

   Dim fsoSysObj    As FileSystemObject
   Dim filFile      As File
   Dim txsStream    As TextStream
   Dim lngErrNum    As Long
   Dim strPath      As String
   Dim strErrText   As String
   
   Set fsoSysObj = New FileSystemObject
   
   ' Store error information.
   lngErrNum = errX.Number
   strErrText = errX.Description
   ' Clear error.
   errX.Clear
   ' Return Windows Temp folder.
   strPath = GetTempDir
   If Len(strPath) = 0 Then
      GoTo LogError_End
   End If
   
   On Error Resume Next
   ' See if file already exists.
   Set filFile = fsoSysObj.GetFile(strPath & APP_ERROR_LOG)
   ' If not, then create it.
   If Err <> 0 Then
      Set filFile = fsoSysObj.CreateTextFile(strPath & APP_ERROR_LOG)
   End If
   On Error GoTo 0

   ' Open file as text stream for reading.
   Set txsStream = filFile.OpenAsTextStream(ForAppending)
   ' Write error information and close.
   With txsStream
      .WriteLine lngErrNum
      .WriteLine strErrText
      If Len(strProcName) > 0 Then .WriteLine strProcName
      .WriteLine Now
      .WriteBlankLines 1
      .Close
   End With

LogError_End:
   Exit Sub
End Sub

To try the LogError procedure, you can call the following procedure. This procedure suspends error handling, then uses the Raise method of the Err object to force an error. It then passes the Err object to the LogError procedure, along with the name of the procedure that caused the error.

Sub TestLogError()
   ' This procedure tests the LogError function.

   On Error Resume Next
   ' Raise an error.
   Err.Raise 11
   ' Log it.
   LogError Err, "TestLogError"
End Sub

Logging Errors to a Text File

See Also

Additional resources