vba read file

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

1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 4.56 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

Reading text files (line by line)

Reading the text file line by line:

Reading text files (read whole file)

Reading the whole text file into a string:

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.

Related Posts