SOLVED

Query related to import text file with the help of VBA

Iron Contributor

Hello Everyone, 

 

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

like - 

Screenshot (233).png

 

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.

7 Replies

@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.

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?

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

@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

@Hans Vogelaar 

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

like -

Screenshot (236).png

best response confirmed by Excel (Iron Contributor)
Solution

@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
I am very very sorry sir.

Next time i will ask question with full details..

And thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:
1 best response

Accepted Solutions
best response confirmed by Excel (Iron Contributor)
Solution

@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

View solution in original post