Search/Extract Data w/in Text File

[Guest]

Hello again! I am searching within a text file for the last row within the
file. After I locate the row I need to separate the data and place into
multiple cells on my worksheet. The code I have is as follows:

Dim TextPath As String
Dim DelimSrchChar As String
Dim METtime As String

Dim DataBuffer As Variant
Dim NCData As Variant

Dim DelimPos As Integer
'===============================================

DelimSrchChar = ","

TextPath = "C:\Temp\Formatted_NC_SN1008.txt"

Open TextPath For Input As #1 'open text file for SN1008

Do While Not EOF(1) 'go while not end of text file

Line Input #1, NCData

If EOF(1) Then
DelimPos = InStr(NCData, DelimSrchChar)

MsgBox DelimPos

Application.Cells(1, 2) = NCData

End If

Loop

Close #1

End Sub

NCData looks like this: 000:03:42:53,3.40426,20.,18.9623. I can get the
data, but I am having trouble finding a method for parsing the data and
placing the parsed data into multiple cells.

The problem: I can use the InStr function to get the data to the left of
the first delimiter as position 13 (i.e. 000:03:42:53) , but how can I get
the data between delimiter positions 13 and 21 (i.e. 3.40426) and between
delimiter positions 21 and 26 (i.e. 20.XX), and to the right of delimiter
position 26 (i.e. 18.9623)? The data sizes can change so they are not
necessarily fixed.

Thanks you.

D.Parker

 

[Guest]

Let's assume that you are parsing on the coma. First read the data into a
single column as text. Then use the text to columns feature to do the
parsing for you.

In the worksheet this is Data > Text to Columns..
In VBA its something like:

Selection.TextToColumns

 

[Guest]

Thank you for the response. My delimiter is the ",", so I would be parsing
on the comma, but are you suggesting to write to a cell and then write to the
columns? I would like to avoid the first action and go directly to placing
the parsed text data in various cells. I think its possible with this
method, I would have to write the data to the cell and rewrite over that cell
when the TextToColumns action is performed I guess.

D.Parker

 

[Guest]

You can avoid over-writing you input data if you wish.

Try the process out manually to get the feel of it. Then use the recorder.
It will help define the many parameters that Selection.TextToColumns requires.

I hope this is of some value to you.

 

[Guest]

It was of very great value to me! Thank you very much, the macro recorder
gave me a better feel.

Thanks again.

D.Parker