vba macro will be placed in a seperate file (macro.xlsm)
and the file where we have to get the data is sample1.xlsx
there are two excel files one is macro.xlsm(in this macro code will be placed) & sample1.xlsx (where we have to get the data from notepad and paste it to sample1.xlsx)
all files are located in different path
Files
https://drive.google.com/open?id=1nqetv ... 0Nf3woOGjA
https://drive.google.com/open?id=1I0fh0 ... xv5xQAda3y
Code: Select all
Option Explicit
Sub Sample()
Dim MyData As String
Dim lineData() As String, strData() As String, myFile As String
Dim i As Long, rng As Range
On Error Resume Next
myFile = "C:UserswsDesktopDF.txt"
Open myFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
' Split into wholes line
lineData() = Split(MyData, vbNewLine)
Set rng = Range("A2")
' For each line
For i = 0 To UBound(lineData)
' Split the line
strData = Split(lineData(i), "|")
' Write to the sheet
rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
Next
Range("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, 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)), _
TrailingMinusNumbers:=True
Worksheets("Sheet1").Columns("A:Z").AutoFit
Range("A1").Select
End Sub