Using VBA to read and write to a text files

This is not limited to .txt files - you can interact with any file type, though ones with standard encoding will be easier.

Here I have compiled two examples of code showing how to read and create files with text.

Note Open has a range of modes to open a file, such as "Input", "Output" and "Append" 

Reading Files with VBA

Sub ReadFile()
    Dim line As String
    Dim fileName As String
    'This is to count through the lines
    Dim i As Integer

    fileName = "C:\temp\people.txt"

    file = FreeFile()
    Open fileName For Input As file

    i = 1

    While Not EOF(file)
        Line Input #file, line
        MsgBox (line)
        i = i + 1
End Sub

We have created a basic text file with a list of names.

The above code was copied into excel.

This creates a message box for each line in the file. We can replace this message box with anything else we want to do with the data in the file such as save it to an array.

Writing Files with VBA

Sub WriteFile()

    Dim fileNameOut As String

    fileNameOut = "C:\temp\people_out.txt"

    Open fileNameOut For Append As #1

        Write #1, "George"
        Write #1, "Brian"
    Close #1

End Sub

Entering this code into the excel VBA.  
This creates the file "people-out.txt" at the fileNameOut location.  Note that since we used "Write" to code is machine readable. If we don't want to include double quotations then we need to use "Print". 

Related Sections

Python - Learn the python from the basics up. This fast track example code course will get you creating powerful python programs in no time.

Must Read Articles

VBA and Microsoft Excel - Getting the most out of excel with VBA programming
VBA and Microsoft Word - Getting the most out of word with VBA programming
Application Object - Using the applications features in your code.
Installing VBA and First Program - What do you need to start using visual basic application VBA.