Import Multiple Text Files into Seperate Excel Rows on the same sheet

Question

I have about 50 text files and they all contain anything between 10-25 lines,

I am looking for a way to import all 50 text files into a single excel sheet with each text files content going into a single row.

So with 50 text files I'd expect 50 rows of text containing anything between 10-25 lines per row.

Please read [*Why is “Can someone help me?” not an actual question?*](https://meta.stackoverflow.com/q/284236/1188513) — Mathieu Guindon, Mar 29 '18 at 14:09

FloLie · Accepted Answer · 2018-03-29T14:58:22.867

For the solution i assume, that you have a list of file pathes to the files you want to read. To achive your desired result i propose to pass an input stream of each file to a String variable and store them in an Array, with length of number of input files. In the end you can write the Array to a Range. As a result one cell will contain the content of each input file split into lines within the cell.

Sub readTxtFiles(filePathList As Variant)
Dim strFileContent As String
Dim iFile As Integer: iFile = FreeFile
'Array with length of number of input files
Dim contentArray As Variant
ReDim contentArray(UBound(filePathList))

i = 0
For Each filePath In filePathList
    'Open file and store content to strFileContent and close file
    Open filePath For Input As #iFile
    strFileContent = Input(LOF(iFile), iFile)
    Close #iFile
    'Store content in output array 
    contentArray(i) = strFileContent
    'Clear content variable and increment i
    strFileContent = ""
    i = i + 1
Next filePath

ThisWorkbook.Sheets(1).Range("A1:A"& Ubound(filePathList)) = contentArray
End Sub

EDIT:

Since you have the file locations also stored in a file, you can use the following code to pass it to an array found here

Function readPathList(path as String) As Variant
    Dim sFile As String, sWhole As String
    Dim v As Variant
    sFile = "C:\mytxtfile.txt"
    Open sFile For Input As #1
    sWhole = Input$(LOF(1), 1)
    Close #1
    v = Split(sWhole, vbNewLine)
    readPathList= v
End Function

and then call

readTxtFiles(readPathList('Path to your list'))

Thanks, however I am unsure exactly where to input the filelist. I have generated the location for all 50 files as a text file also. — Sukh, Mar 29 '18 at 14:27

Import Multiple Text Files into Seperate Excel Rows on the same sheet

1 Answers1