importing 2 text files at once

Question

I want to import 2 txt files at once to excel through vba. Currently, I can only import 1 txt file. I want the users to be able the ability to just choose 2 files to be imported.

Sub ImportFiles()

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim path As String
    Dim filename As String
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    With fd
    
        'Set the initial path to the C:\ drive.
        .InitialFileName = ActiveWorkbook.path
        'Add a filter that includes  the list.
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt", 1                                                   
        'The user pressed the button.
        If .Show = -1 Then
        
            For Each vrtSelectedItem In .SelectedItems
                path = Left(vrtSelectedItem, InStrRev(vrtSelectedItem, "\"))
                filename = Right(vrtSelectedItem, Len(vrtSelectedItem) - InStrRev(vrtSelectedItem, "\"))
               
                 Call Importfile(path, filename)

            Next vrtSelectedItem
        Else
        End If
    End With

    Set fd = Nothing

End Sub


Sub Importfile(path As String, filename As String)
    Sheets.Add(After:=Sheets("Sheet1")).Name = "Data"
    On Error Resume Next
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & path & filename, Destination:=Range("$A$1"))
        .Name = filename
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileOtherDelimiter = vbTab
        .TextFileDecimalSeparator = "."
        .TextFileThousandsSeparator = " "
        .Refresh BackgroundQuery:=False
    End With
End Sub

I understand that I need to use a loop to loop it to choose 2 files to be imported. But how do i do so?

Answer 1

You need to allow the users to select multiple files, you can do that by adding the AllowMultiSelect option to the file dialog.

Sub ImportFiles()

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim path As String
    Dim filename As String
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    With fd
        .AllowMultiSelect = True
        'Set the initial path to the C:\ drive.
        .InitialFileName = ActiveWorkbook.path
        'Add a filter that includes  the list.
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt", 1                                                   
        'The user pressed the button.
        If .Show = -1 Then
        
            For Each vrtSelectedItem In .SelectedItems
                path = Left(vrtSelectedItem, InStrRev(vrtSelectedItem, "\"))
                filename = Right(vrtSelectedItem, Len(vrtSelectedItem) - InStrRev(vrtSelectedItem, "\"))
               
                 Call Importfile(path, filename)

            Next vrtSelectedItem
        Else
        End If
    End With

    Set fd = Nothing

End Sub