Forum Discussion

Excel's avatar
Excel
Iron Contributor
May 30, 2022
Solved

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? ...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 30, 2022

    Excel 

    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's avatar
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's avatar
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's avatar
    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
    • Excel's avatar
      Excel
      Iron Contributor
      May 30, 2022

      HansVogelaar 

      Sir, if we do not want repetitive headers, then what should we write ?

      like -

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 30, 2022

        Excel 

        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's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 30, 2022

    Excel Sorry, I can't as I have moved away from VBA. Rarely use it.

Resources