Query related to import text file with the help of VBA

Hello Everyone,

I try a VBA code to import text file to excel and it works.

like -

But i want to import different text file in same sheets.

So, what should i write in VBA code?

Please help..

Here is a attached file.

Why didn't you mention that at the beginning? It was not apparent from your screenshot.

Public Sub ImportTextFile()
    Dim TargetSheet As Worksheet
    Dim NewRow As Long
    Dim TextFile As Workbook
    Dim OpenFiles() As Variant
    Dim i As Integer
    Dim n As Long
    Set TargetSheet = ThisWorkbook.Worksheets.Add ' or ActiveSheet
    NewRow = 1
    OpenFiles = GetFiles()
    Application.ScreenUpdating = False
    For i = 1 To UBound(OpenFiles)
        Set TextFile = Workbooks.Open(OpenFiles(i))
        With TextFile.Sheets(1).Range("A1").CurrentRegion.Offset(n)
            .Copy Destination:=TargetSheet.Range("A" & NewRow)
            NewRow = NewRow + .Rows.Count - n
        End With
        If i = 1 Then n = 1
        TextFile.Close
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Public Function GetFiles() As Variant
    GetFiles = Application.GetOpenFilename(Title:="Select File(s) to Import", MultiSelect:=True)
End Function

Riny_van_Eekelen

Platinum Contributor

May 30, 2022

Excel Unless you are desperate to learn VBA, I would recommend that you move into PowerQuery, which has a built-in connector that allows you to get data from multiple files in a folder. And it puts everything in one single table.

Excel

Iron Contributor

May 30, 2022

Yes sir, but i am practicing in VBA code.
Sir, please help me to import import different text file in same sheet with the help of VBA code?

HansVogelaar

MVP

May 30, 2022

Excel

Do you want to copy the contents of all text files into one sheet, one below the other? If so:

Public Sub ImportTextFile()
    Dim TargetSheet As Worksheet
    Dim NewRow As Long
    Dim TextFile As Workbook
    Dim OpenFiles() As Variant
    Dim i As Integer
    Set TargetSheet = ThisWorkbook.Worksheets.Add ' or ActiveSheet
    NewRow = 1
    OpenFiles = GetFiles()
    Application.ScreenUpdating = False
    For i = 1 To UBound(OpenFiles)
        Set TextFile = Workbooks.Open(OpenFiles(i))
        With TextFile.Sheets(1).Range("A1").CurrentRegion
            .Copy Destination:=TargetSheet.Range("A" & NewRow)
            NewRow = NewRow + .Rows.Count
        End With
        TextFile.Close
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Public Function GetFiles() As Variant
    GetFiles = Application.GetOpenFilename(Title:="Select File(s) to Import", MultiSelect:=True)
End Function

Forum Discussion

Query related to import text file with the help of VBA

Share

Resources