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 inside the Excel VBA editor.

Press Alt F11. Goto Tools - References

Reference import text

Now choose the Microsoft Scripting Runtime from the list.

Scripting Runtime import text

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.

Option Explicit

Sub ReadTextintoExcel() 'Excel VBA to import the text (txt) files in a list to Excel.
Const Path = "C:\Test\Text\" 'change the path accordingly
Dim objFSO As FileSystemObject
Dim objTS As TextStream

Dim i As Long
Dim strFile as string

Application.ScreenUpdating=False
Set objFSO=Createobject("Scripting.FileSystemObject")

For i=10 To Cells(Rows.Count, 2).End(xlUp).Row
strFile=Cells(i, 2) If Len(Dir(Path & Cells(i, 2))) > 0 Then
Set objTS=objFSO.OpenTextFile(Path & strFile, ForReading, False, TristateUseDefault)
Sheet2.Cells(i, 1).Value=objTS.ReadAll
objTS.Close
Else
Sheet2.Cells(i, 1).Value="Not Found"
End If
Next i Application.ScreenUpdating=True

End Sub


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