vba read file

VBA Read file in Excel (txt, xml, csv, binary)

1 Star2 Stars3 Stars4 Stars5 Stars (10 votes, average: 4.60 out of 5)

You can VBA Read file binary or text data using using the Open operator. For CSV files and other structured data schemas you can use the Jet.OLEDB driver or Microsoft Queries.

Text/binary files are common ways of storing data as opposed to databases or regular Excel files. Looking at various resources I missed a single resource which would demonstrate the various methods for PROPERLY reading files in VBA.

It is important to remember that you shouldn’t read all files using the same approach. Be aware of the structure of the file. If it is a structured CSV use the ADODB connection, if you need to read only a couple of rows read the file row by row or by chunks, else read the whole file. If you want performance – always select the right approach.

Reading text files in VBA

VBA Read text files (line by line)

To read an entire text file line by line use the code below.

VBA Read text files (read whole file)

To read an entire text file in one go (not line by line) use the code below.a

VBA Read specific number of lines from a text file

In cases when you want to read specific lines from a text file you can adapt the line by line read code as below. It allows you to read a certain number of lines (noLines) from a text file from a specific start line number (sLine). If you set noLines to 0 it will read all lines till end of the file.

Reading CSV files in VBA

Reading CSV files (read whole file and process each row)

Reading a text file line by line into a string:

Reading CSV files (whole file to Worksheet)

Read whole file to an Excel Worksheet:

To refresh the CSV upload (in case the CSV was updated) simply run:

Reading binary files in VBA

With Binary files often you will be using objects which are not of fixed byte length like Integers. For example you would want to read Strings from binary files together with other data types. In such cases use the Type object data type when writing to a file. Learn more here.
Below a simple example of reading a file to which a Type data type was saved to, including an Integer and String.

Reading XML files in VBA

Functions needed to read files in VBA

Function Description
FreeFile Get next free file number available for the Open statement / FileOpen function. Using this function is important especially when operating on multiple files simultaneously.
More info here.
BOF(fileNumber) Returns true if you are at the beginning of the file described by the file number.
More info here.
EOF(fileNumber) Returns true if you have reached the end of the file described by the file number. More info here.
Loc(fileNumber) Returns the current read/write position within an open file. More info here.
LOF(fileNumber) Returns the size in bytes of the file represented by the file number. More info here.

VBA Read File Summary

Reading files in VBA is not hard and requires just a few lines of code usually. It is, however, important to use the appropriate approach to read a file in VBA. Not all files need to be read line-by-line which is usually inefficient. Equally so you need not always read the entire file if you just need the first few / last rows. Working with XML files is also not a challenge if you read through my post on how to work with XML files.