botmortty
07-21-2017, 11:35 AM
I need to extract specific numbers from a large text file and copy in excel sheet.
total rows will be about 60
I need to copy the number after text string resistance and offset 1983619836number after text string offset
I was able to do some code but it stops at row 2.
so far this is what I have
Private Sub CommandButton1_Click()
Dim myFile As String, text As String, textline As String, i As Integer, upRes As Integer, downRes As Integer, upOff As Integer, downOff As Integer, temp() As String
i = 1
myFile = Application.GetOpenFilename()
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
Do While i < 61
temp() = Split(text, "Offset (down) ")
upRes = InStr(text, "Resistance (up) ")
downRes = InStr(text, "Resistance (down) ")
upOff = InStr(text, "Offset (up) ")
downOff = InStr(text, "Offset (down) ")
Cells(i, 1).Value = i
Cells(i, 2).Value = Mid(text, upRes + 17, 9)
Cells(i, 3).Value = Mid(text, downRes + 19, 9)
Cells(i, 4).Value = Mid(text, upOff + 12, 8)
Cells(i, 5).Value = Mid(text, downOff + 14, 8)
i = i + 1
text = temp(1)
Loop
End Sub
Your help will be appreciated!
1983619836
total rows will be about 60
I need to copy the number after text string resistance and offset 1983619836number after text string offset
I was able to do some code but it stops at row 2.
so far this is what I have
Private Sub CommandButton1_Click()
Dim myFile As String, text As String, textline As String, i As Integer, upRes As Integer, downRes As Integer, upOff As Integer, downOff As Integer, temp() As String
i = 1
myFile = Application.GetOpenFilename()
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
Do While i < 61
temp() = Split(text, "Offset (down) ")
upRes = InStr(text, "Resistance (up) ")
downRes = InStr(text, "Resistance (down) ")
upOff = InStr(text, "Offset (up) ")
downOff = InStr(text, "Offset (down) ")
Cells(i, 1).Value = i
Cells(i, 2).Value = Mid(text, upRes + 17, 9)
Cells(i, 3).Value = Mid(text, downRes + 19, 9)
Cells(i, 4).Value = Mid(text, upOff + 12, 8)
Cells(i, 5).Value = Mid(text, downOff + 14, 8)
i = i + 1
text = temp(1)
Loop
End Sub
Your help will be appreciated!
1983619836