CODEWITHSUNDEEP
excelvbatext
Annabanana
Annabanana

Reputation: 95

How to import multiple text files saved to a specific folder into excel on one worksheet one column, different row for each file?

I have a folder that contains about 50 small txt files. What I need is to import them into a SINGLE Excel sheet one under another. I should start with:

textfile1.txt

line1
line2
line3
line4

textfile2.txt

line1
line2
line3
line4

textfile3.txt

line1
line2
line3
line4

textfile4.txt

line1
line2
line3
line4

and so on...

What I would like to end up with is:

It should basically look like a directory with a file name in column A and THE ENTIRE TEXT of a corresponding file in the same row of a column B. The text of an entire file needs to be pasted into ONE cell.

Upvotes: 1

Views: 633

Answers (1)

JosephC
JosephC

Reputation: 929

This will read all files in the directory specified, and read them as text files outputting the file name in column A and the contents in column B on sheet 1 (starting on row 2). Note: No error checking included.

Sub Test()
    Call ScanDir("C:\temp\test2\")
End Sub


Private Sub ScanDir(ByVal DirPath As String)
    Dim oCurFile As String
    oCurFile = Dir(DirPath)

    Dim oCurRow As Long
    oCurRow = 2
    Dim oFile As String
    Do While oCurFile <> ""
        Open DirPath & oCurFile For Input As #1
        oFile = Input(LOF(1), 1)
        Close #1

        Sheet1.Cells(oCurRow, 1).Value = oCurFile
        Sheet1.Cells(oCurRow, 2).Value = oFile

        oCurFile = Dir()
        oCurRow = oCurRow + 1
        oFile = ""
    Loop
End Sub

Upvotes: 2

Related Questions