VBA Text Files

There are a few ways to work with text files in VBA. There are built-in statements and functions, the Scripting Runtime library's TextStream class, and the ActiveX Data Objects library's Stream class. For working with text files using a specific character encoding use the ActiveX Data Objects library's Stream class.

Built-In File Handling

VBA's built-in file-handling statements and functions provide the ability to perform CRUD operations on text files. Files can be opened in a variety of ways depending on the situation. Open a file using the Open statement, read, write, or append to the file, and then close the file using the Close statement.

Statement/Procedure	Description
Open	Opens a file for input and/or output.
FreeFile	Returns the next available file number for use with the Open statement.
Width	Sets the output line width for an open file.
Close	Closes a file opened with the Open statement.
Reset	Closes all files opened with the Open statement.
Lock	Prevents access to all or part of a file by other processes.
Unlock	Allows access to all or part of a file by other processes.
Print	Writes display-formatted data to a sequential file.
Spc	Used with Print or Debug.Print to position output using spaces.
Tab	Used with Print or Debug.Print to position output using tabs.
Write	Writes data to a sequential file. Strings are quoted.
Put	Writes data from a variable to a file.
Get	Reads data from a file to a variable.
Line Input	Reads a line from an open sequential file into a String variable.
Input	Reads data from an open sequential file into variables.
Input$	Reads data as strings from an open sequential file into variables.
InputB	Reads data from a binary file into variables.
InputB$	Reads data as strings from a binary file into variables.
Name	Renames a file or directory.
FileCopy	Copies a file.
Kill	Deletes files.
EOF	Returns True when the end of a file opened for input has been reached.
LOF	Returns the size in bytes of an open file.
Loc	Returns the current read/write position within an open file.
Seek	Sets the position for the next read/write operation within an open file.
FileLen	Returns the length of a file in bytes.
FileDateTime	Returns the date and time when a file was created or last modified.
FileAttr	Returns the file mode of an open file.
SetAttr	Sets the attributes of a file.
GetAttr	Returns the attributes of a file or directory.
Dir	Returns the name of a file or directory that matches a given pattern, file attribute, or the volume label of a drive. If not found, returns an empty string.
Chdir	Changes the current directory.
Chdrive	Changes the current drive.
Curdir	Returns the current path as Variant/String.
Curdir$	Returns the current path as String.
Mkdir	Creates a directory.
Rmdir	Deletes a directory.

Open Statement

Syntax

Open filepath For filemode [accessmode] [lock] As #filenumber [Len = recordlength]

File Mode

Files can be opened for Append, Binary, Output, Input, or Random.

Append mode is for appending to the end of text files.
Binary mode is for reading and writing bytes.
Ouput mode is for writing to a text file and overwriting any content currently in the file.
Input mode is for reading from text files.
Random access is for reading and writing fixed-length records in a non-sequential way.

Note: For files opened for Append or Output, the file must be closed before it can be opened again using a different file number. For files opened for Binary, Input, or Random, files can be opened using a different file number without closing the currently open file.

Access Mode

An access mode can be specified for files opened for Binary or Random access.

Access Read allows for reading.
Access Write allows for writing.
Access Read Write allows for reading and writing.

Lock

A lock can be specified to control access to a file by outside processes.

Lock Read prevents outside processes from reading from the file.
Lock Write prevents outside processes from writing to the file.
Lock Read Write prevents outside processes from reading from or writing to the file.
Lock Shared allows outside processes to read from and write to the file.

File Number

The file number can be an integer between 1 and 511 inclusive. The next available file number is obtained using the FreeFile function.

Record Length

For random access files, the record length is the number of bytes (<= 32,767) in a record. For sequential files, the record length is the number of characters buffered.

Read And Write Text

To overwrite all text in a text file, open the text file for Output. To write to the end of a text file, open the text file for Append. To read text from a text file, open the text file for Input.

Option Explicit

Public Sub WriteToTextFile()

    Dim FilePath As String
    FilePath = Environ$("USERPROFILE") & "\Desktop\Example.txt"

    Dim FF As Integer
    FF = FreeFile()

    Open FilePath For Output As #FF

    Print #FF, "Hello, World!"
    'Write #FF, "Hello, World!"

    Close #FF

End Sub 

Public Sub AppendToTextFile()

    Dim FilePath As String
    FilePath = Environ$("USERPROFILE") & "\Desktop\Example.txt"

    Dim FF As Integer
    FF = FreeFile()

    Open FilePath For Append As #FF

    Print #FF, "Hello, World!"
    'Write #FF, "Hello, World!"

    Close #FF

End Sub

Public Sub ReadFromTextFile()

    Dim FilePath As String
    FilePath = Environ$("USERPROFILE") & "\Desktop\Example.txt"

    Dim FF As Integer
    FF = FreeFile()

    Open FilePath For Input As #FF

    Do While Not EOF(FF)
        Dim LineText As String
        Line Input #FF, LineText
        Debug.Print LineText
    Loop

    Close #FF

End Sub

Read and Write Bytes

To read and write Bytes, open a file for Binary access and specify Access Read, Access Write, or Access Read Write.

Option Explicit

Public Sub WriteToBinary()

    Dim FilePath As String
    FilePath = Environ$("USERPROFILE") & "\Desktop\Example.txt"

    Dim FF As Integer
    FF = FreeFile()

    Dim ByteArr() As Byte
    ByteArr = "Hello, World!"

    Open FilePath For Binary Access Write As #FF

    Put #FF, , ByteArr

    Close #FF

End Sub

Public Sub ReadFromBinary()

    Dim FilePath As String
    FilePath = Environ$("USERPROFILE") & "\Desktop\Example.txt"

    Dim FF As Integer
    FF = FreeFile()

    Open FilePath For Binary Access Read As #FF

    Dim ByteArr() As Byte
    ReDim ByteArr(0 To LOF(FF) - 1) As Byte

    Get #FF, , ByteArr

    Close #FF

    Dim FileContents As String
    FileContents = ByteArr

    Debug.Print FileContents

End Sub

Read and Write Records

To read and write records, open a file for Random access and specify Access Read, Access Write, or Access Read Write.

Option Explicit

Public Type ExampleType
    Message As String * 15
    Flag    As Boolean
End Type

Public Sub WriteToRandom()

    Dim FilePath As String
    FilePath = Environ$("USERPROFILE") & "\Desktop\Example.dat"

    Dim FF As Integer
    FF = FreeFile()

    Dim ET As ExampleType
    ET.Message = "Hello, World!"
    ET.Flag = True

    Dim RecordNumber As Long
    RecordNumber = 1

    Open FilePath For Random Access Write As #FF Len = Len(ET)

    Put #FF, RecordNumber, ET

    Close #FF

End Sub

Public Sub ReadFromRandom()

    Dim FilePath As String
    FilePath = Environ$("USERPROFILE") & "\Desktop\Example.dat"

    Dim FF As Integer
    FF = FreeFile()

    Dim ET As ExampleType

    Dim RecordNumber As Long
    RecordNumber = 1

    Open FilePath For Random Access Read As #FF Len = Len(ET)

    Get #FF, RecordNumber, ET

    Close #FF

    Debug.Print ET.Message, ET.Flag

End Sub

Microsoft Scripting Runtime Library

The Microsoft Scripting Runtime library is helpful for working with the file system and text files. The TextStream class represents a text file and has properties and methods which make working with text files much easier:

Member	Description
AtEndOfLine	Returns True if the file pointer immediately precedes the end-of-line marker in a TextStream.
AtEndOfStream	Returns True if the file pointer is at the end of a TextStream.
Column	Returns the column number of the current character position in a TextStream.
Line	Returns the current line number in a TextStream.
Close	Closes an open TextStream.
Read	Reads a given number of characters from a TextStream and returns a string.
ReadAll	Reads an entire TextStream and returns a string.
ReadLine	Reads a line from a TextStream and returns a string.
Skip	Skips a given number of characters when reading a TextStream.
SkipLine	Skips the next line when reading a TextStream.
Write	Writes a string to a TextStream.
WriteBlankLines	Writes a given number of newline characters to a TextStream.
WriteLine	Writes a string and a newline character to a TextStream.

The FileSystemObject and File classes can be used to retrieve a TextStream object:

Method	Description
FileSystemObject.CreateTextFile	Creates a text file and returns a TextStream object.
FileSystemObject.OpenTextFile	Opens a text file and returns a TextStream object.
File.OpenAsTextStream	Opens file and returns a TextStream object.

Microsoft ActiveX Data Objects Library

The Microsoft ActiveX Data Objects library contains the Stream class which can be used to work with text files. A specific character encoding can be set using the Charset property of the Stream class.

Stream Members
Member	Description
Charset	The character set used to store text in the Stream.
EOS	True if the current position is the end of the Stream.
LineSeparator	The line separator character for the Stream.
Mode	The permissions for modifying data in a Stream.
Position	The current position within a Stream.
Size	The size of the Stream in bytes.
State	The state of the Stream as a member of the ObjectStateEnum.
Type	The type of data in the Stream as a member of the StreamTypeEnum. Binary or Text.
Cancel	Cancels the execution of a pending asynchronous method call.
Close	Closes an open object and any dependent objects.
CopyTo	Copies a given number of characters or bytes in the Stream to another Stream.
Flush	Send the remaining contents of the Stream buffer to the object associated with the Stream.
LoadFromFile	Loads the contents of a file into a Stream.
Open	Opens the Stream to allow the manipulation of binary or text data.
Read	Reads a given number of bytes from a binary Stream.
ReadText	Reads a given number of characters from a text Stream.
SaveToFile	Saves the binary contents of a Stream to a file.
SetEOS	Sets the position that marks the end of the Stream.
SkipLine	Skips one line when reading a text Stream.
Write	Writes binary data to a the Stream.
WriteText	Writes a string of text to the Stream.

UTF-8

When writing UTF-8 text, note that the Stream object will write the file with a BOM (Byte Order Mark). To write UTF-8 text without a BOM the text must be copied to another Stream as binary starting from position 3 (BOM is 3 bytes in length and file position is 0-indexed).

Public Sub WriteToTextFileUTF8(FilePath As String, TextContent As String)

    Dim S As Object 'ADODB.Stream
    Set S = CreateObject("ADODB.Stream")

    Dim S1 As Object 'ADODB.Stream
    Set S1 = CreateObject("ADODB.Stream")

    With S
        .Type = 2 'adTypeText
        .Charset = "UTF-8"
        .Open
        .WriteText TextContent
        .Position = 3
        With S1
            .Type = 1 'adTypeBinary
            .Open
            S.CopyTo S1
            .SaveToFile FilePath, 2 'adSaveCreateOverWrite
            .Close
        End With
        .Close
    End With

End Sub

To read UTF-8 text set the Charset property to "UTF-8".

Public Function ReadFromTextFileUTF8(FilePath As String) As String

    Dim ADOStream As Object
    Dim OutputText As String

    Set ADOStream = CreateObject("ADODB.Stream")

    With ADOStream
        .Charset = "UTF-8"
        .Open
        .LoadFromFile FilePath
        OutputText = .ReadText()
    End With

    ADOStream.Close
    Set ADOStream = Nothing

    ReadFromTextFileUTF8 = OutputText

End Function