is it possible... (multiple input+naming)

[rbmcclen]

Is it possible to make a macro that inputs all text files from the
directory the workbook is located in and put the files name
(text01.txt) as the first cell of the column the files data is inputed
into? resulting in something like this:

test01.txt test02.txt test03.txt
xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx

 

[Bernie Deitrick]

Try the macro below. Store it in a workbook in the folder with the text files that you want to put
into one file.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateTextFiles()
Dim myBook As Workbook
Dim myCalc As XlCalculation

With Application
.EnableEvents = False
.DisplayAlerts = False
myCalc = .Calculation
.Calculation = xlCalculationManual
End With

On Error Resume Next
With Application.FileSearch
.NewSearch
'This workbook must be stored in the folder of interest
.LookIn = ThisWorkbook.Path
.SearchSubFolders = False
.Filename = "*.txt"
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets(1).Range("A1").CurrentRegion.Copy _
ThisWorkbook.Sheets(1).Cells(2, i)
ThisWorkbook.Sheets(1).Cells(1, i).Value = _
Replace(.FoundFiles(i), .LookIn & "\", "")
myBook.Close False
Next i
Else: MsgBox "There were no files found."
End If
End With
With Application
.EnableEvents = True
.DisplayAlerts = True
.Calculation = myCalc
End With

End Sub