Input/Output Files

VBA already includes commands to allow data to read or write to external text files. This is more commonly known as I/O (Input / Output) and is used to store files in the formats such as ‘txt’, ‘csv’ and ‘ini’ files.

Example of Output Data:

Sub BuildTextFile()
    Dim fnum

    fnum = FreeFile()
    Open "C:\vba.txt" For Output As #fnum

    Write #fnum, "Excel VBA", "Day 1"
    Write #fnum, "Excel VBA", "Day 2"
    Write #fnum, "Excel VBA Workshop Q&A", "Day 3"

    Close #fnum
End Sub

The above example creates an instance of a file using the FreeFile function, which returns a unique number (as its handler). The Open method is used to locate and open the file.

The Output property tells the system that data is to be written to the named file using the pointer #fnum.

The Write method adds line-by-line data to the pointer and then is lost with the Close method.

Even if the file name does not exist, it will create this file in the specified path but the path must exist.

If the filename already exists, this routine will overwrite (no prompt) and the previous file will be lost.

The file generated is a 'txt' file:

Example of Input Data:

Sub ReadTextFile()
    Dim fnum
    Dim strField1 As String, strField2 As String

    fnum = FreeFile()
    Open "C:\vba.txt" For Input As #fnum

    Do Until EOF(fnum)
        Input #fnum, strField1, strField2
        Debug.Print strField1 & " : " & strField2
    Loop

    Close #fnum
End Sub

The above example uses the Input property instead to change the direction of the flow of data (read from).

Using the EOF method, the procedure loops through the delimiter line break until it reaches the end of the file.

To view the results, open the Immediate Window (Ctrl + G) before running the above procedure:

The above two examples demonstrates how to read and write data to and from external files and will require a little more coding to deal with interaction and variables to make this more flexible (and practical).

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: FreeFile, Do...Loop, Write, Input, Output, EOF, Dubug.Print & Close.