Add text from files in a directory to new sheet

[Jacomatic]

Hi,
I'm trying to create a macro that will paste fixed width text from files in a directory to a single new worksheet.
I found the macro below that does most of what I need, but instead of pasting each no file to the last row, it pastes to the last column.
Any help would be greatly appreciated.

Sub GetFiles()
Dim sPath As String, sName As String
Dim i As Long, qt As QueryTable
With ThisWorkbook
.Worksheets.Add After:= _
.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = Format(Now, "dd-mmm-yy")
sPath = "D:\Data\Desktop\Subscriber List\ACS\"
sName = Dir(sPath & "*.*")
i = 0
Do While sName <> ""
i = i + 1
Cells(i, 1).Value = sName
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & sPath & sName, Destination:=Cells(i, 1))
.Name = Left(sName, Len(sName) - 4)

.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 1, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 1, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 5, 2, 2, 5, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileFixedColumnWidths = Array(1, 2, 8, 9, 16, 8, 1, 1, 3, 20, 15, 6, 6, 1, 28, 10, 2, 28, 4, 2, 4, 10, 28, 2, 5, 1, 8, 28, 10, 2, 28, 4, 2, 4, 10, 28, 2, 5, 1, 4, 2, 13, 66, 1, 1, 31, 35, 16, 1, 1, 8, 1, 1, 8, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 1, 81, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

sName = Dir()
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next
Loop
End Sub

 

[Jacomatic]

I think I figured out how to get the data from the text files to paste below the last row, but it seems to only pull 2 of the 5 files that match "P2822502*.*" in the "D:\Data\Desktop\Subscriber List\ACS\" directory.
Also, how can I skip the first and last rows of data when importing the text files?

Sub ImportACSFiles()
Dim sPath As String, sName As String
Dim lastRow As Long, qt As QueryTable
With Sheets("ACS-Updates")
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
End With

sPath = "D:\Data\Desktop\Subscriber List\ACS\"
sName = Dir(sPath & "P2822502*.*")
Do While sName <> ""

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & sPath & sName, Destination:=Cells(lastRow, 2))
.Name = Left(sName, Len(sName) - 4)

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 1, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 1, 1, 2, 2, 2, 2, 1, 2, 2, 2, 2, 5, 2, 2, 5, 2, 2, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileFixedColumnWidths = Array(1, 2, 8, 9, 16, 8, 1, 1, 3, 20, 15, 6, 6, 1, 28, 10, 2, 28, 4, 2, 4, 10, 28, 2, 5, 1, 8, 28, 10, 2, 28, 4, 2, 4, 10, 28, 2, 5, 1, 4, 2, 13, 66, 1, 1, 31, 35, 16, 1, 1, 8, 1, 1, 8, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 1, 81, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

sName = Dir()
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next
Loop
End Sub