Transfering Required data from *.txt file to

[Gurpreet]

I retriving data from the machine printer to the txt file using th
RS232 interface , all the data which I am getting is stored in th
*.txt file. I woul like to request for help as to how I can pick th
required data from txt file to excel sheet because if i simply ope
that *.txt file in exl the complete line forms only one cell (String
and numeric values comes in one cell only) where as I would like t
pick the numeric values only from that txt file. Can anybody help me i
writing the macro for that or can advise me as to how I can get th
numeric data only from the txt file to excel file.

I would like to mention one example:- the data I ge from the txt fil
in one cell is

" Total Production of day = 150 Kg."
" Total yarn cuts for 1000 meters = 120"

Now if I open this in excel, The complete one line comes in one cel
only where as in the above two lines I require "1000" from the fir
line and "120" from the second line ( I do not want any alphanumeric t
be picked along with that

 

[Rob van Gelder]

Here's some code. Hasn't been through a lot of testing.
Probably would have been better using VBScript Regular Expression library.

Sub test()
Debug.Print GetLineNumeric(Replace(Range("A1").Value, """", ""), "=", 2)
End Sub

Function GetLineNumeric(Text As String, TextAfter As String, LineNum As
Long) As String
Const cLineSep = vbLf
Dim lngLine As Long, i As Long, j As Long, k As Long, strTemp As String,
strCandidate As String

lngLine = 1
i = 1
Do Until lngLine = LineNum
i = InStr(i, Text, cLineSep)
If i = 0 Then Exit Do Else i = i + 1
lngLine = lngLine + 1
Loop

If i > 0 Then
j = InStr(i, Text, cLineSep)
strTemp = Mid(Text, i, IIf(j = 0, Len(Text) - i + 1, j - i))
i = InStr(1, strTemp, TextAfter)
If i > 0 Then strTemp = Mid(strTemp, i + 1)
i = 1
Do Until i = 0
j = InStr(i, strTemp, " ")
strCandidate = Mid(strTemp, i, IIf(j = 0, Len(strTemp) + 1, j) -
i)
If IsNumeric(strCandidate) Then
GetLineNumeric = strCandidate
Exit Do
Else
i = j + 1
End If
Loop
End If
End Function