[Solved] Import Multiple Text Files into Seperate Excel Rows on the same sheet [closed]

by


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'))

1

solved Import Multiple Text Files into Seperate Excel Rows on the same sheet [closed]