Import Multiple Text Files to One Spreadsheet

[gcmischief]

Hi guys,

I'm looking for a way to import multiple text files into one excel spreadsheet, with the files being imported one below the other. I'm currently using CPearson's code to let the user select and import a text file to an excel spreadhseet. However there are three text files that I want to import into the sheet every month, with each text file imported below the previous one. And as mentioned the user has to be able to select the files as the directories will change each month as well.

The text files are tab delimited and will have the same number of columns per file (18 in total) but the number of rows will vary.

My VBA knowledge is limited but I realise using multiselect=true in the application.getopenfilename code will let me pick more than one file and return them as an array, but I'm uncertain if the file select code registers that it is now an array or how would I then get the import code to loop through said array?

I've attached the import and file select codes from cpearson's website for your convenience.

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = 1
RowNdx = 6

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub


Sub DoTheImport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt")
    If FileName = False Then
       Exit Sub
    End If
    Sep = vbTab
    If Sep = vbNullString Then
       Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ImportTextFile FName:=CStr(FileName), Sep:=CStr(Sep)
End Sub

Any help you can offer would be greatly appreciated. Thanks!