I need some guidance on how to handle a multi-line text file import to
excel.

I have a multi-line text file i am attempting to parse into Excel2003.
Here is an example of the file:


<rlnrp:cell=all;
NEIGHBOUR RELATION DATA


CELL
WA32300


CELLR DIR CAND CS
WA31340 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELLR DIR CAND CS
WA30780 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELL
WA30433


CELLR DIR CAND CS
WA31960 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


END


---------------------------------------------------------------
Details of where i am trying to go with this:


The format of the source file is not modifiable so i am left to deal
with it as is. I had created a macro to handle the basic import a space

delimited and then another macro to sort it the way i wanted. This
worked until the text file exceeded 65000 lines in excel. Now it has
become an issue. What i ultimately need to achieve is a way to define a

Start and Stop where all data between Start and Stop is read in as 1
line. It would identify CELL as the Start and read in to 1 line until
it comes across CELL (Stop) again. Then continues on. I just need the
CELL and CELLR data. Thus looking like so:


CELL CELLR
WA32300 WA31340 WA30780
WA30433 WA31960


......and so on down the file.


So, either some sort of Start and Stop trigger to define when to start
writing a new line into Excel or a means of sending a string to a
specific range (column) within Excel. So, any help or guidance you can
offer will be much appreciated.


Thank you

I would use VBA to read the text file and populate Excel. I think the below
should give you an idea where to start. The below code reads the text file
and puts the references into a new book. Where I have hard coded the file
name you should link this to a cell value. The code runs from a button put
into an empty sheet


Private Sub CommandButton1_Click()
Dim Fn, r, c As Long
Dim LineOfText As String
Dim xlBook As Excel.Workbook

r = 1
c = 1
Set xlBook = Application.Workbooks.Add

Fn = FreeFile()
Open "c:\cell.txt" For Input As Fn
While Not EOF(Fn)
Line Input #Fn, LineOfText

If Left$(LineOfText, 5) = "CELLR" Then
c = c + 1
Line Input #Fn, LineOfText
ActiveSheet.Cells(r, c).Value = Left$(LineOfText, 7)
ElseIf Left$(LineOfText, 4) = "CELL" Then
r = r + 1
c = 1
Line Input #Fn, LineOfText
ActiveSheet.Cells(r, c).Value = Left$(LineOfText, 7)
End If
Wend

Close #Fn
End Sub
--
Tony Green


"Waxaholic" wrote:

I need some guidance on how to handle a multi-line text file import to
excel.

I have a multi-line text file i am attempting to parse into Excel2003.
Here is an example of the file:


<rlnrp:cell=all;
NEIGHBOUR RELATION DATA


CELL
WA32300


CELLR DIR CAND CS
WA31340 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELLR DIR CAND CS
WA30780 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELL
WA30433


CELLR DIR CAND CS
WA31960 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


END


---------------------------------------------------------------
Details of where i am trying to go with this:


The format of the source file is not modifiable so i am left to deal
with it as is. I had created a macro to handle the basic import a space

delimited and then another macro to sort it the way i wanted. This
worked until the text file exceeded 65000 lines in excel. Now it has
become an issue. What i ultimately need to achieve is a way to define a

Start and Stop where all data between Start and Stop is read in as 1
line. It would identify CELL as the Start and read in to 1 line until
it comes across CELL (Stop) again. Then continues on. I just need the
CELL and CELLR data. Thus looking like so:


CELL CELLR
WA32300 WA31340 WA30780
WA30433 WA31960


......and so on down the file.


So, either some sort of Start and Stop trigger to define when to start
writing a new line into Excel or a means of sending a string to a
specific range (column) within Excel. So, any help or guidance you can
offer will be much appreciated.


Thank you

Awesome. Thank you very much. This will certainly save me some time.