Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 37 Β· Files, Databases and APIs

Text and CSV Files in VBA

Read and write text and CSV files in VBA with Open, Line Input, Print, FreeFile, TextStream and ADODB.Stream for UTF-8, and parse CSV data safely.

Text files are the universal exchange format. Logs, configuration files, exported reports and CSV data from other systems all arrive as text. VBA can read and write them with the classic Open statement, with the FileSystemObject TextStream, or with ADODB.Stream when you need UTF-8.

This lesson shows the three approaches, how to write and parse CSV properly (quotes and commas inside fields), and how to move CSV data to and from a worksheet quickly.

VBA cannot run on this site, so VBA listings are read-only. The runnable stand-ins are JavaScript versions of the CSV parser and writer, where the tricky logic lives.

The classic Open statement

VB
Sub WriteLog()
    Dim f As Integer: f = FreeFile          ' next free file number
    Open ThisWorkbook.Path & "\log.txt" For Append As #f
    Print #f, Format(Now, "yyyy-mm-dd hh:nn:ss") & " Started"
    Write #f, "Ada", 42, True               ' Write adds quotes and commas
    Close #f
End Sub

Sub ReadLines()
    Dim f As Integer: f = FreeFile
    Dim line As String
    Open "C:\Data\notes.txt" For Input As #f
    Do While Not EOF(f)
        Line Input #f, line
        Debug.Print line
    Loop
    Close #f
End Sub

The modes: For Input (read), For Output (write, overwrites), For Append (add to the end), For Binary (raw bytes), For Random (fixed records). Always use FreeFile rather than a fixed #1, and make sure every Open has a matching Close, including in your error handler.

Reading a whole file at once is quicker than line by line:

VB
Dim f As Integer, all As String
f = FreeFile
Open "C:\Data\notes.txt" For Binary Access Read As #f
all = Space$(LOF(f))
Get #f, , all
Close #f
Dim lines() As String: lines = Split(all, vbCrLf)

Open treats the file as ANSI (the system code page), so accented or non-Latin characters can break.

TextStream through FileSystemObject

VB
Dim fso As Object, ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.OpenTextFile("C:\Data\out.txt", 2, True)   ' 1=read, 2=write, 8=append
ts.WriteLine "First line"
ts.Close

Set ts = fso.OpenTextFile("C:\Data\out.txt", 1)
Do Until ts.AtEndOfStream
    Debug.Print ts.ReadLine
Loop
ts.Close

The last argument of OpenTextFile (Format) selects ASCII (0), Unicode UTF-16 (-1) or the system default (-2).

UTF-8 with ADODB.Stream

VB
Dim st As Object: Set st = CreateObject("ADODB.Stream")
st.Type = 2                 ' text
st.Charset = "utf-8"
st.Open
st.WriteText "Zoë, 東京" & vbCrLf
st.SaveToFile "C:\Data\utf8.txt", 2    ' 2 = overwrite
st.Close

st.Open
st.LoadFromFile "C:\Data\utf8.txt"
Debug.Print st.ReadText
st.Close

The UTF-8 output of ADODB.Stream includes a BOM (byte order mark) at the start; many tools accept it, but some do not.

CSV: reading and writing

A CSV field may contain commas, quotes or line breaks if it is wrapped in double quotes, and a quote inside is doubled: "She said ""hi""". Plain Split(line, ",") breaks on such fields, so use a real parser when data comes from outside.

VB
Function CsvEscape(ByVal s As String) As String
    If InStr(s, ",") > 0 Or InStr(s, """") > 0 Or InStr(s, vbLf) > 0 Then
        s = """" & Replace(s, """", """""") & """"
    End If
    CsvEscape = s
End Function

Sub ExportCsv()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
    Dim data As Variant: data = ws.UsedRange.Value2
    Dim f As Integer: f = FreeFile
    Dim r As Long, c As Long, parts() As String

    Open ThisWorkbook.Path & "\export.csv" For Output As #f
    For r = 1 To UBound(data, 1)
        ReDim parts(1 To UBound(data, 2))
        For c = 1 To UBound(data, 2)
            parts(c) = CsvEscape(CStr(data(r, c)))
        Next c
        Print #f, Join(parts, ",")
    Next r
    Close #f
End Sub

For simple, trusted files Excel can open CSV directly: Workbooks.Open "file.csv" or Workbooks.OpenText with a delimiter. To import into a sheet without opening: ws.QueryTables.Add (a text connection), or read lines and split them into an array, then write the array to the sheet in one assignment.

Try it: a CSV parser and writer

JS Parse CSV with quotes

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Write CSV with escaping and read a log

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Using For Output when you meant For Append, wiping the old file.
  • Forgetting Close #f (the file stays locked), especially after an error.
  • Splitting CSV lines on commas when fields can contain quotes.
  • Saving text with Open and losing non-English characters; use ADODB.Stream with UTF-8.
  • Losing leading zeros: Excel converts 00123 to 123 when opening CSV; import that column as text.

Check your understanding

Quick check Β· 4 questions

of 4 answered You scored / 4 Β· perfect!

  1. 1. Why use FreeFile?

    FreeFile returns the next available file number, avoiding conflicts with other open files.
  2. 2. Which Open mode adds lines at the end of an existing file?

    Append writes at the end; Output would overwrite the file.
  3. 3. How is a double quote written inside a quoted CSV field?

    CSV doubles the quote character inside a quoted field.
  4. 4. Which object helps write UTF-8 text files in VBA?

    ADODB.Stream lets you pick a character set, unlike the classic Open statement.

Summary

  • Open ... For Input/Output/Append with FreeFile, Line Input, Print, Write and Close is the classic approach.
  • OpenTextFile (FSO) offers an object API; ADODB.Stream handles UTF-8.
  • CSV needs quoting rules: escape on write, parse quotes on read.
  • Move data between sheets and files with arrays for speed, and close files in every code path.

Next: Databases and Web APIs from VBA