How to read a text file into excel vba

Asked 12 years, 3 months ago. Active 12 years, 3 months ago. Viewed 5k times. Community Bot 1 1 1 silver badge. Fink Fink 3, 18 18 silver badges 25 25 bronze badges. Add a comment. Active Oldest Votes. A few observations: I'm nervous about having a variable named "file" when File is a class within the Scripting Runtime; Do Until file.

AtEndOfLine stops almost immediately: you're at the end of a line as soon as you've read one. I think you want Do Until file. AtEndOfStream the rest of your code looks OK, although I'd move all the stuff about adding lines to arrays into a method on your report class is the file physically local?

Or are you reading from a network drive? That might account for the problem. If so, consider reading the whole thing into a string and splitting it. ReadLine Do Until. Mike Woodhouse Mike Woodhouse 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. See also Proper VBA error handling. Read, Write, or Read Write Lock — optional. Closes the file using the file number. More info here. Connect and share knowledge within a single location that is structured and easy to search.

I have a text file with data entries only integers. There is one entry per line in the text file. I was wondering how to transfer that data into an array in VBA. The Val function turns the string into an number value and then vba casts it to an integer for you. Afterwards, you have int values in an array.

The code will stop once the file is compete or the array has values in it. How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Collectives on Stack Overflow.

Learn more. Asked 10 months ago. Active 8 months ago. Viewed 1k times. I once asked at Vbscript extract data from Text File into Excel But after trying for few weeks and still no success so I use vba instead. Cells Rows. Count, "A". End xlUp. Cells nextrow, "A". Cells nextrow, "B". Cells nextrow, "C". Cells nextrow, "D". Cells nextrow, "E". Cells nextrow, "F". Cells nextrow, "G". JRashid JRashid 23 6 6 bronze badges. How is data formatted in the file? Is there a column separator?

Try s[plitting that line by ". Hi porkaloca. I just update my input text file and there's no column separator — JRashid. Hi DaMahdi Tried before, unfortunately that doesnt work but I believe I may be wrong. Could you share a link to one of your text files e. VSWR W Show 7 more comments. Active Oldest Votes. Text to Excel Note that this will generate over