Excel vba open read text file

This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No.

Any additional feedback? In this article. Always the name of a FileSystemObject. 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.

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. 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.

With Binary files often you will be using objects which are not of fixed byte length like Integers. Now that you have a line from your text file, you need to do something with it. However, the line will still have all the commas in it.

So the first line for us will be this:. You need to parse the lines from your text file in some way. A good way to parse a line is with the Split function you met earlier. By using Split, you can place each item from a line into an array:. Between the round brackets of Split , we have the variable we want to split, which is LineFromFile. After a comma, we have the separator we want to look for.

The separator is the comma, for us. When Split has finished, we'll have an array called LineItems. Our text file always has three items per line first name, last name, ISBN. So we know the array goes from 0 to 2 positions. We can now go ahead and place each item into a cell on the spreadsheet:. Between the round brackets of Offset we have the row number and the column number.

We set this to 0 earlier. We'll increment this variable shortly. The columns are always offset at 0, 1 and 2. A value of 0, remember, keeps you in the same column. A value of 1 moves you 1 column over, and a value of 2 moves you 2 columns over from the ActiveCell. To the right of the equal sign, we have our LineItems array. We next have LineItems 1 , which will get us the last name in the B column.

Finally, we have LineItems 0 , which will get us the first name in the C column. When you open a file, you should close it somewhere in your code. This is fairly straightforward:. You type the word Close and then, after a space, the file number you're trying to close. Test it out. Make sure any cell in the A column is the Active cell on your spreadsheet the A column is the one your formatted to Text.