Text Files: Read, Write, Append

Reading, writing, and appending to and from text files are common tasks in VBA. They're not very difficult tasks, but I got tired of looking up the syntax of the various Get, Open, Print, Close, etc. statements every time I needed to deal with files.

I wrote the three functions below to make text file processing easier.

FileRead

This function reads the contents of a text file into a string variable. It reads the entire contents of the file into a string variable in a single operation. This makes it fast for small to medium sized files, but not ideal for very large text files (like some log files).

For large text files, you're better off reading and processing the file as a "stream." With that approach, each file read operation is limited to a portion of the file. This requires less memory, but is slower because of the repeated disk access.

FileWrite

Like FileRead above, this function works using a single disk operation. The same advantages and disadvantages apply here, too. This is very fast for small to medium size files, but you would not want to write a huge string to a file in one shot.

To keep things simple, this function overwrites any existing file without warning. (Consider this your warning.)

FileAppend

This function is similar to FileWrite, except instead of overwriting an existing file it adds the passed string to the end of the file. The function adds an implicit line break to the destination file after the passed string. This makes the function especially suitable for use when creating ad hoc log files.

Usage

As long as you understand their limitations, these functions are fine for incorporating into your applications. Where I use them the most, though, is in the immediate window.

Displaying the contents of a large string variable

The immediate window displays only 200 lines of text. It's easy to overwhelm this limit. When I have a string thats too large for the immediate window, I'll simply do something like this:

FileWrite "C:\Temp\Dump.txt", MyVeryLongString

Then, I just highlight the full path, press [Ctl] + [Win] + [G] on my keyboard, and the file opens for me in Notepad++ (my default text editor).

Parsing a large string and viewing the results

If I'm parsing some large chunk of text, I can do something similar by combining FileRead with FileWrite. Perhaps I have an excerpt from a large text file that I want to test. I copy the lines I want from the source text file, paste it into a new file named Excerpt.txt, import it using FileRead, and save the parsed text back out to a file named Output.txt.

Txt = FileRead("C:\Temp\Excerpt.txt")
FileWrite "C:\Temp\Output.txt", ParseMyString(Txt)

Ad hoc logging

Finally, if I want to do some quick and dirty logging, I might put together a function like this:

Sub Log(Msg As String)
    FileAppend "C:\Temp\Log.txt", VBA.Now & vbTab & Msg
End Sub

If I run the following code...

Log "My": Log "name": Log "is": Log "Mike"

...I get the following output...

(Hey, look! ^^^ Comment continuation using ellipses!)

The Code

The three functions are shown below. Additionally, I've included two other functions that these three depend on to run. I've included links to the original articles for each of those functions in the code comments.

By the way, you won't find On Error statements in most of my code. It's not that I don't use error handling, I've just evolved beyond all that awful boilerplate.

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

'Returns the contents of file FName as a string
Function FileRead(FName As String) As String
Dim FNum As Integer, Result As String
    
    Result = Space(FileLen(FName))
    FNum = FreeFile
    Open FName For Binary Access Read As #FNum
    Get #FNum, , Result
    Close FNum
    FileRead = Result

End Function

'Writes a text file with the contents of a string
'   - Creates the file if it does not exist
'   - Overwrites the contents of an existing file without warning
'   - Returns true if successful
Function FileWrite(FName As String, Contents As String) As Boolean
    If Not DeleteFile(FName) Then Exit Function
    Dim FNum As Integer
    FNum = FreeFile()
    Open FName For Output As FNum
    'trailing semi-colon needed to prevent adding blank line at end of file
    '  see: http://stackoverflow.com/a/9445141/154439
    Print #FNum, Contents;
    Close #FNum
    FileWrite = True
End Function

'Appends the contents to the end of a file
' - if the file does not exist, it is created
' - a new line is implicitly added after the contents
'   `- this means that FileAppend may be repeatedly called without passing any vbCrLf's
Sub FileAppend(FName As String, Contents As String)
    If Not FileExists(FName) Then
        'File does not exist, create new via FileWrite
        FileWrite FName, Contents & vbCrLf
    Else
        Dim FNum As Integer
        FNum = FreeFile()
        Open FName For Append Access Write As #FNum
        Print #FNum, Contents
        Close #FNum
    End If
End Sub


'https://nolongerset.com/the-subtle-dangers-of-dir/
Private Function FileExists(FullPathOfFile As String) As Boolean
    Dim HasWildcards As Boolean
    HasWildcards = (InStr(FullPathOfFile, "*") > 0) Or (InStr(FullPathOfFile, "?") > 0)
    
    If HasWildcards Then
        FileExists = (Len(Dir(FullPathOfFile)) > 0)
    Else
        Dim oFSO As New Scripting.FileSystemObject
        FileExists = oFSO.FileExists(FullPathOfFile)
    End If
End Function

'https://nolongerset.com/kill-failed-let-user-try-again/
Function DeleteFile(FName As String, _
                    Optional DelayInSeconds As Long = 0, _
                    Optional Silent As Boolean = False) As Boolean
    Dim StartTime As Date, Complete As Boolean
    On Error Resume Next
    StartTime = Now()
    Do Until Complete
        Err.Clear
        Kill FName
        If Not FileExists(FName) Then
            DeleteFile = True
            Complete = True
            Exit Function
        End If
        If Err.Number <> 0 Then
            If (Now() - StartTime) * 86400 > DelayInSeconds Then
                If Not Silent Then
                    If MsgBox("Unable to delete file:" & vbCrLf & vbCrLf & _
                              FName & vbCrLf & vbCrLf & _
                              "Ensure the file is closed and you have the permissions to delete it.", _
                              vbRetryCancel, "File Delete Failed") = vbCancel Then
                        DeleteFile = False
                        Complete = True
                        Exit Function
                    End If
                Else
                    Complete = True
                End If
            Else
                Sleep 1000 ' wait for 1 second before trying again
            End If
        End If
    Loop
End Function

Referencing the Microsoft Scripting Runtime

Note that my FileExists function includes an early-binding reference to the FileSystemObject (Dim oFSO As New Scripting.FileSystemObject).

Many VBA developers prefer to use late-binding for everything, including accessing the FileSystemObject. I'm no longer one of them. While I minimize the number of references in my projects, I always include the Microsoft Scripting Runtime:

Including "Microsoft Scripting Runtime" gets us early-bound access to Dictionaries and the FileSystemObject

Early-binding provides IntelliSense and a small performance boost. And since the Scripting Runtime is available on every version of Windows going back to at least Windows XP, you don't need to worry about deploying it the way you would a typical third-party library.

Image by 41330 from Pixabay

UPDATE [2022-10-05]: Add section about referencing the Microsoft Scripting Runtime library.

UPDATE [2024-04-13]: Add explicit return type (As Boolean) to the FileExists() function.