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.
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.
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.)
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.
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 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.
#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) 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:
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.
UPDATE [2022-10-05]: Add section about referencing the Microsoft Scripting Runtime library.