Visual Basic for Applications/Use Log Files from VBA

Summary

At times it is useful to write strings to a text file from VBA. For example, for listing files, their hashes, or for simply logging errors. Text files are here intended to mean files with the .txt suffix. There are several procedures listed in the code module for both writing and reading such files.

Writing to Text Files and Logs

• The procedure SendToLogFile APPENDS a string to a text file. The user optionally selects his own path and file name, but there is no OVERWRITE choice with this method. If user parameters are not given then the defaults are used. This procedure places the parameter string in line with a time date string, with each record entry on a new line.
• The procedure LogError1 is intended to APPEND log errors, and is an example of the Print# statement. It is assumed here that the log file will always be placed in the same folder as the calling Workbook. As such, no path check is needed, and the minimum of coding applies. All formatting of the parameter text is assumed to be done externally. Readers can find format details for Print# in VBA help, and might also care to compare the advantages of using the Write# statement instead.
• The procedure LogError2 is also intended to APPEND log errors and performs the same task as LogError1. It is however an example of the OpenTextFile method of the Scripting object. This procedure needs a reference in the VBA editor to Microsoft Scripting Runtime. Notice that this log will write every successive record into the first line unless vbNewLine characters are included at the end of the parameter string itself.
• Procedure WriteToFile REPLACES any existing text, as opposed to appending it to any existing entries.
• There are conventions in logging. Logging with a text file (.txt) means placing each record on the same line with the individual fields separated by a single tab character. The number of fields is the same for each record. Another convention is to use a comma-separated file format (.csv) where the fields are separated by commas instead of tabs. Both of these formats can be imported into MS Office applications,though users should pay particular attention as to how different log writing methods handle quotes.

Reading Text Files and Logs

• VBA can also read text files into code for processing. However, once the notion of reading files is introduced, the choice of writing formats becomes more important. In addition, file reading can place more demands on error handling, and testing for path integrity.
• The procedure GetAllFileText returns the entire contents of a .txt file . Readers should first confirm that the text file exists. File utilities elsewhere in this series would suit this purpose.
• The procedure GetLineText returns an array of text file lines. The same comments regarding early file checks also apply in this case.

VBA Code

Option Explicit

Sub TestSendToLogFile()
    'Run this to test the making of a log entry
    Dim sTest As String
    
    'make a test string
    sTest = "Test String"
    
    'calling procedure - path parameter is optional
    SendToLogFile sTest

End Sub

Function SendToLogFile(ByVal sIn As String, Optional sLogFilePath As String = "") As Boolean
    'APPENDS the parameter string and a date-time string to next line of a log file
    'You cannot overwrite this file; only append or read.
    'If path parameter not given for file, or does not exist, defaults are used.
    'Needs a VBA editor reference to Microsoft Scripting Runtime
        
    Dim fs, f, strDateTime As String, sFN As String
    
    'Make a date-time string
    strDateTime = Format(Now, "dddd, mmm d yyyy") & " - " & Format(Now, "hh:mm:ss AMPM")
    
    'select a default file name
    sFN = "User Log File.txt"
    
    'Create a scripting object
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    'if path not given then get a default path instead
    If sLogFilePath = "" Then
        sLogFilePath = ThisWorkbook.Path & "\" & sFN
    Else
        'some path was provided - so continue
    End If
    
    'Open file for appending text at end(8)and make if needed(1)
    On Error GoTo ERR_HANDLER
        'set second arg to 8 for append, and 1 for read.
        Set f = fs.OpenTextFile(sLogFilePath, 8, 1)
    Err.Clear
        
    'write to file
    f.Write sIn & vbTab & strDateTime & vbCrLf
    
    'close file
    f.Close

    SendToLogFile = True
    Exit Function

ERR_HANDLER:
    If Err.Number = 76 Then 'path not found
        
        'make default path for output
        sLogFilePath = ThisWorkbook.Path & "\" & sFN
        
        'Open file for appending text at end(8)and make if needed(1)
        Set f = fs.OpenTextFile(sLogFilePath, 8, 1)
        
        'resume writing to file
        Resume Next
    Else:
        If Err.Number <> 0 Then
            MsgBox "Procedure SendToLogFile has a problem : " & vbCrLf & _
            "Error number : " & Err.Number & vbCrLf & _
            "Error Description : " & Err.Description
        End If
        Exit Function
    End If

End Function

Function LogError1(sIn As String) As Boolean
    'APPENDS parameter string to a text file
    'assumes same path as calling Excel workbook
    'makes file if does not exist
    'no layout or formatting - assumes external
    
    Dim sPath As String, Number As Integer
    
    Number = FreeFile 'Get a file number
    sPath = ThisWorkbook.Path & "\error_log1.txt" 'modify path\name here
    
    Open sPath For Append As #Number
    Print #Number, sIn
    Close #Number

    LogError1 = True
    
End Function

Function WriteToFile(sIn As String, sPath As String) As Boolean
    'REPLACES all content of text file with parameter string
    'makes file if does not exist
    'no layout or formatting - assumes external
    
    Dim Number As Integer
    
    Number = FreeFile 'Get a file number
    
    'write string to file
    Open sPath For Output As #Number
    Print #Number, sIn
    Close #Number

    WriteToFile = True
    
End Function

Function LogError2(sIn As String) As Boolean
    'Scripting Method - APPENDS parameter string to a text file
    'Needs VBA editor reference to Microsoft Scripting Runtime
    'assumes same path as calling Excel workbook
    'makes file if does not exist
    'no layout or formatting - assumes external
    
    Dim fs, f, sFP As String
    
    'get path for log
    sFP = ThisWorkbook.Path & "\error_log2.txt"
    
    'set scripting object
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    'make and open file
    'for appending text (8)
    'make file if not exists (1)
    Set f = fs.OpenTextFile(sFP, 8, 1)
            
    'write record to file
    'needs vbNewLine charas added to sIn
    f.Write sIn '& vbNewLine
        
    'close file
    f.Close

    LogError2 = True
    
End Function

Sub TestGetAllFileText()
    'run this to fetch text file contents
    
    Dim sPath As String, sRet As String, vRet As Variant
    
    sPath = "C:\Users\Your Folder\Documents\test.txt"
    
    'check that file exists - see file utilities page
    'If FileFound(sPath) Then
        If GetAllFileText(sPath, sRet) = True Then
            MsgBox sRet
        End If
    'Else
       'MsgBox "File not found"
    'End If

End Sub

Function GetAllFileText(sPath As String, sRet As String) As Boolean
    'returns all text file content in sRet
    'makes use of Input method
    
    Dim Number As Integer

    'get next file number
    Number = FreeFile

    'Open file
    Open sPath For Input As Number

    'get entire file content
    sRet = Input(LOF(Number), Number)
    
    'Close File
    Close Number

    'transfers
    GetAllFileText = True

End Function

Sub TestGetLineText()
    'run this to fetch text file contents
    
    Dim sPath As String, sRet As String, vRet As Variant
    Dim n As Long
    sPath = "C:\Users\Internet Use\Documents\test.txt"
    
    'check that file exists - see file utilities page
    'If FileFound(sPath) Then
        'print text files lines from array
        If GetLineText(sPath, vRet) = True Then
            For n = LBound(vRet) To UBound(vRet)
                Debug.Print vRet(n)
            Next n
        End If
    'Else
       'MsgBox "File not found"
    'End If

End Sub

Function GetLineText(sPath As String, vR As Variant) As Boolean
    'returns all text file lines in array vR
    'makes use of Input method
    
    Dim Number As Integer, sStr As String
    Dim vW As Variant, sF As String, n As Long
    
    'redim array
    ReDim vW(0 To 1)
    
    'get next file number
    Number = FreeFile

    'Open file
    Open sPath For Input As #Number

    'loop though file lines
    Do While Not EOF(Number)
        n = n + 1
        Line Input #Number, sStr
        ReDim Preserve vW(1 To n)
        vW(n) = sStr
        'Debug.Print sStr
    Loop
    
    'Close File
    Close #Number
    
    'transfers
    vR = vW
    GetLineText = True

End Function