Opening the book…
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
The book could not be loaded just now.