this should do it.... install in your master workbook, name the main sheet Master and edit the fPath to where your text files are stored.
Option Explicit
Sub FilesToColumns()
'Import multiple text files and copy specific data into a single column from each file
Dim fPATH As String, fNAME As String
Dim NC As Long, wsMaster As Worksheet
fPATH = "C:\2013\TextFiles\" 'path to files, remember the final \
Set wsMaster = ThisWorkbook.Sheets("Master") 'master sheet in this workbook
NC = wsMaster.Cells(1, Columns.Count).End(xlToLeft).Column + 1 'next empty column
fNAME = Dir(fPATH & "*Report.txt")
If Len(fNAME) > 0 Then
Application.ScreenUpdating = False
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "temp"
Do While Len(fNAME) > 0
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fPATH & fNAME, Destination:=Range("$A$1"))
.Name = "Report"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
wsMaster.Cells(1, NC).Value = Range("B2").Value
wsMaster.Cells(2, NC).Resize(2).Value = Range("B4:B5").Value
wsMaster.Cells(4, NC).Resize(2325).Value = Range("C10:C2334").Value
ActiveSheet.Range("A:M").Delete xlShiftToLeft
fNAME = Dir
NC = NC + 1
Loop
wsMaster.Columns.AutoFit
Application.DisplayAlerts = False
Sheets("temp").Delete
Application.ScreenUpdating = True
End If
End Sub
Bookmarks