StellarFinance
New Member
- Joined
- Apr 14, 2018
- Messages
- 8
Hello Friends,
Requesting your help with VBA code to loop through a directory (simple folder holding a group of .txt files) to perform the below function on each .txt file. I was able to get the code to perform the function (shown below), but no idea how to create the code which will open each .txt file and have the contents copied and formatted onto an excel sheet. There may be 30+ files, hence, the loop is welcome.
FUNCTION TO BE PERFORMED--
Sub CopyFromNotePad()
'Copy .txt file into Excel
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = Workbooks.Open("INSERT EXCEL FILE PATH")
Set wsI = wbI.Sheets("Sheet2")
Set wbO = Workbooks.Open("INSERT.TXT FILE PATH")
wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False
Columns("A:A").Select
Selection.TexttoColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Tab:=True, _
Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
Requesting your help with VBA code to loop through a directory (simple folder holding a group of .txt files) to perform the below function on each .txt file. I was able to get the code to perform the function (shown below), but no idea how to create the code which will open each .txt file and have the contents copied and formatted onto an excel sheet. There may be 30+ files, hence, the loop is welcome.
FUNCTION TO BE PERFORMED--
Sub CopyFromNotePad()
'Copy .txt file into Excel
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = Workbooks.Open("INSERT EXCEL FILE PATH")
Set wsI = wbI.Sheets("Sheet2")
Set wbO = Workbooks.Open("INSERT.TXT FILE PATH")
wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False
Columns("A:A").Select
Selection.TexttoColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Tab:=True, _
Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub
