PDA

View Full Version : Extract string from text file



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

snb
07-21-2017, 12:42 PM
sub M_snb()
sn=split(createobject("scripting.filesytemobject").opentextfile("G:\OF\sample report.txt").readall,vbcrlf)

msgbox join(filter(sn,"Resistance"),"_") & vblf & join(filter(sn,"Offset"),"_")
end sub