Visual Basic for Applications/Use Log Files from VBA
Summary[edit | edit source]
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[edit | edit source]
- 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[edit | edit source]
- 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[edit | edit source]
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