Import Text File with Excel VBA
In Excel one of the cool features is that you can import files csv, txt, mdb into Excel pretty quickly and relatively easily, if of course you have some VBA. I once answered a post online where the person wanted to import text files but the entire contents of the text file had to go into once cell. I was not sure this could be done but upon research it surely can be done.
I created a list of the files that I wanted to be imported into my Excel file. Then I set up a link to Microsoft's Scripting Runtime library.
Here is how to do that.
Press Alt F11. Goto Tools - References
Now choose the Microsoft Scripting Runtime from the list.
Now your Excel VBA back end is set up as it should be with the MS Scripting runtime selected.
The following is the VBA code to import a text file into a single cell with vba. Each file imported will go into a new cell.
Sub ReadTextintoExcel() 'Excel VBA to import the text (txt) files in a list to Excel.
Dim objTS As TextStream
Dim i As Long
Dim strFile as string
For i=10 To Cells(Rows.Count, 2).End(xlUp).Row
I have put together the following Excel file which already has a reference to Microsoft Scripting Runtime and the file has the above VBA coding. I have included an example txt file to show what source data was used in this process. Make sure you change the file path above to suit