May 30 2022 05:03 AM
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.
May 30 2022 05:09 AM
@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.
May 30 2022 05:23 AM
May 30 2022 05:28 AM
@Excel Sorry, I can't as I have moved away from VBA. Rarely use it.
May 30 2022 05:40 AM
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
May 30 2022 06:03 AM
May 30 2022 06:07 AM
SolutionWhy 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
May 30 2022 06:18 AM
May 30 2022 06:07 AM
SolutionWhy 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