You can VBA Read file binary or text data using a couple of different approaches in Excel. VBA provides you a set of native statements like Open to open and ready files. However in this article aside from showing you these native approaches to reading files using Excel Macros you can read CSV files and other structured data schemas using Jet.OLEDB driver, Microsoft Queries or also the FileSystemObject.
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.
Dim fileName As String, textData As String, textRow As String, fileNo As Integer fileName = "C:\text.txt" fileNo = FreeFile 'Get first free file number Open fileName For Input As #fileNo Do While Not EOF(fileNo) Line Input #fileNo, textRow textData = textData & textRow Loop Close #fileNo
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
Dim fileName As String, textData As String, fileNo As Integer fileName = "C:\text.txt" fileNo = FreeFile 'Get first free file number Open fileName For Input As #fileNo textData = Input$(LOF(fileNo), fileNo) Close #fileNo
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.
Dim fileName As String, textData As String, textRow As String, fileNo As Integer Dim lineCounter as Long, sLine as Long, noLines as Long fileName = "C:\text.txt" sLine = 20 'number of the first line you want to read noLines = 100 'number of lines you want to read fileNo = FreeFile Open fileName For Input As #fileNo Do While Not EOF(fileNo) Line Input #fileNo, textRow If lineCount >= sLine and ((noLines > 0 and lineCount < noLines + sLine) or noLines = 0) then textData = textData & textRow End If lineCount = lineCount + 1 Loop Close #fileNo
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:
'Assuming file looks like this. File path: C:\test.csv '"Col1", "Col2", "Col3" '1 , 2 , 3 directory = "C:\" fileName = "test.csv" 'Assuming test.csv is in C:\ directory Set rs = CreateObject("ADODB.Recordset") strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & directory & ";" _ & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";" strSQL = "SELECT * FROM " & fileName rs.Open strSQL, strcon, 3, 3 rs.MoveFirst Do col1 = rs("Col1") col2 = rs("Col2") col3 = rs("Col3") rs.MoveNext Loop Until rs.EOF
Reading CSV files (whole file to Worksheet)
Read whole file to an Excel Worksheet:
Dim ws as Worksheet, destRng as Range, fileName as String fileName = "C:\text.txt" Set destRng = Range("A1") Set ws = ActiveSheet With ws.QueryTables.Add(Connection:= "TEXT;" & fileName & "", Destination:=destRng) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 852 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote 'Select your delimiter - selected below for Comma .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileTrailingMinusNumbers = True 'This will refresh the query End With
To refresh the CSV upload (in case the CSV was updated) simply run:
Reading binary files in VBA
Dim fileName As String, fileNo As Integer, intVar As Integer fileName = "C:\text.bin" fileNo = FreeFile Open fileName For Binary Lock Read As #fileNo Get #fileNo, , intVar Close #fileNo
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.
Type TestType intVar As Integer strVar As String End Type Sub ReadBinary() Dim fileName As String, fileNo As Integer, testVar As TestType fileName = "C:\test.bin" fileNo = FreeFile Open fileName For Binary Lock Read As #fileNo Get #fileNo, , testVar Debug.Print testVar.intVar 'Print the Integer Debug.Print testVar.strVar 'Print the String Close #fileNo End Sub
Reading XML files in VBA
XML files are basically text files which follow the XML taxonomy. You can try to read and process XML files similarly as text files shown above. However, given you will probably want to extract specific XML tag or attribute information I suggest reading my dedicated article below.
Functions needed to read files in VBA
|Open [path_to_file] For [Mode] [Access] [Lock] As [long_variable]||Opens the file for read/write and returns the # file number (needs to be type of long) into long_variable
More info here. Parameters below:
|Close||Closes the file using the # file number.
More info here.
|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.|
Above functions allow native upload of file data. However for more complicated scenario you will probably go for the FileSystemObject.
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.
Want to Write to files instead?
If you are looking to write to files instead using VBA, read my article on how to write to files using VBA.