multi-line text file import to excel

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.