importing 2 text files at once

user_14989578 0
Viewed 44

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?

vba excel
2 Answers

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
user_28500260

On the documentation site for VBA you find that.

Sub UseFileDialogOpen()
 
    Dim lngCount As Long
 
    ' Open the file dialog
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show
 
        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            MsgBox .SelectedItems(lngCount)
        Next lngCount
 
    End With
 
End Sub

So it seems that you just need to add the .count attribute to the .SelectedItems. Maybe you´ll need to adjust .AllowMultiSelect to true that you can select multiple files at once in the filtedialog.

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.
        .AllowMultiSelect = True
        .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 lngCount = 1 To .SelectedItems.Count
                path = Left(.SelectedItems(lngCount), InStrRev(.SelectedItems(Count), "\"))
                filename = Right(.SelectedItems(Count), Len(.SelectedItems(Count)) - InStrRev(.SelectedItems(Count), "\"))
               
                 Call Importfile(path, filename)

            Next lngCount
        Else
        End If
    End With

    Set fd = Nothing

End Sub
user_91569790
Related