Tom Ogilvy provided me with a fantastic snippet that imported in a similar
file. I have tried to modify the previous script to handle the new text
file layout however i have been recieving a runtime error 1004 amongst
others when i have been playing with this. I have put this basically back to
orig snippet provided, with a couple of mods.

Please can you guys just have a look over the script see if you can see the
glaring mistakes i have made whilst trying to modify Tom's script.
I have included some sample text at the end of the post .

---------------------------------------------------------

Sub GETINVRPT()

Dim FName As String
Dim FNum As Long
Dim l As String
Dim l1 As Variant
Dim s As String
Dim sChr As String
Dim rng1 As Range, rng As Range
Dim cell As Range, iloc As Long

Columns("A:E").ClearContents
Columns(5).NumberFormat = _
"0000000000000"
FName = "C:\INVRPT.txt"

FNum = FreeFile

Open FName For Input As FNum
Line Input #FNum, s
s = Application.Clean(s)
s = Replace(s, Chr(9), "")
l = s
l = Replace(l, "LIN+", "LIN+,")
l = Replace(l, "LOC", "LIN+LOC")
l = Replace(l, ":EN'QTY+17:", ",")
l = Replace(l, "::9'QTY+17:", ",")
l = Replace(l, "::9'QTY+198:", ",")
l = Replace(l, "::9'QTY+83:", ",")
l = Replace(l, "'", "")
' l = Replace(l, "+", ",")
l1 = Split(l, "LIN+")
Cells(1, 1).Resize(UBound(l1) - _
LBound(l1) + 1).Value = Application. _
Transpose(l1)
Close #FNum
Rows(1).Delete
Columns(1).Replace "++", ","
Columns(1).TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array( _
Array(1, 1), _
Array(2, 1), _
Array(3, 1), _
Array(4, 1), _
Array(5, 1), _
Array(6, 1))
Set rng1 = Cells(Rows.Count, 5).End(xlUp)
iloc = InStr(1, rng1, "UN", vbTextCompare)
rng1 = Left(rng1, iloc - 1)
Set rng = Columns(1).SpecialCells(xlConstants)
For Each cell In rng
iloc = InStr(1, cell, "+", vbTextCompare)
iloc = InStr(iloc + 1, cell, "+", vbTextCompare)
cell.Value = "'" & Mid(cell, iloc + 1, 13)
Next
Set rng = Columns(1).SpecialCells(xlBlanks)
rng.Formula = "=" & rng(1).Offset(-1, 0).Address(0, 0)
Set rng = Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
rng.Formula = rng.Value
Set rng = Columns(2).SpecialCells(xlBlanks)
rng.EntireRow.Delete
Columns(2).Delete
Rows(1).Insert
Range("A1:E1").Value = _
Array("LOC", "EAN", "QTY17", "QTY198", "QTY83")
Columns("A:E").AutoFit
Range("A1").CurrentRegion.Name = "Database"
End Sub

----------------------------------------------------------------------

Results i am after:

LOC | EAN | QTY17 | QTY198 | QTY83

0000000000000 | 0000000000000 | 0 | 0 | 0

from a table in the above format i have already created a pivot with lookups
to make a very readable report,

Any help really appreciated.

Brian


---------------------------------------------------------------------------
Sample Text.

Note: I cut ou a massive chunk in the middle but kept the format...

UNB+UNOA:3+5023949000004:14+5014838000001+060205:0 513+436+ETRADING+INVRPT'UNH+23+INVRPT:D:96A:UN:EAN 008'BGM+35+00000009+9'DTM+366:20060204:102'NAD+BY+ 5023949000004::9'NAD+SU+5014838000001::9'LIN+1++21 298776:EN'QTY+17:1'LOC+14+5023949771634::9'QTY+198 :0'LOC
+14+5023949771634::9'QTY+83:0'LOC+14+5023949771634 ::9'LIN+2++21326806:EN'QTY+17:3'LOC+14+50239490578 95::9'QTY+198:0'LOC+14+5023949057895::9'QTY+83:0'L OC+14+5023949057895::9'QTY+17:4'LOC+14+50239491367 74::9'QTY+198:0'LOC+14+5023949136774::9'QTY+83:0'L OC+14+
5023949136774::9'QTY+17:2'LOC+14+5023949182579::9' QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+14+ 5023949182579::9'QTY+17:5'LOC+14+5023949223920::9' QTY+198:0'LOC+14+5023949223920::9'QTY+83:0'LOC+14+ 5023949223920::9'QTY+17:4'LOC+14+5023949248730::9' QTY+19
8:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+502394 9248730::9'QTY+17:3'LOC+14+5023949294535::9'QTY+19 8:0'LOC+14+5023949294535::9'QTY+83:0'LOC+14+502394 9294535::9'QTY+17:3'LOC+14+5023949319342::9'QTY+19 8:0'LOC+14+5023949319342::9'QTY+83:0'LOC+14+502394 931934
2::9'QTY+17:4'LOC+14+5023949373414::9'QTY+198:0'LO C+14+5023949373414::9'QTY+83:0'LOC+14+502394937341 4::9'QTY+17:3'LOC+14+5023949374976::9'QTY+198:0'LO C+14+5023949374976::9'QTY+83:0'LOC+14+502394937497 6::9'QTY+17:1'LOC+14+5023949414768::9'QTY+198:0'LO C+14+5
023949414768::9'QTY+83:0'LOC+14+5023949414768::9'Q TY+17:3'LOC+14+5023949423933::9'QTY+198:0'LOC+14+5 023949423933::9'QTY+83:0'LOC+14+5023949423933::9'Q TY+17:3'LOC+14+5023949584122::9'QTY+198:0'LOC+14+5 023949584122::9'QTY+83:0'LOC+14+5023949584122::9'Q TY+17:
3'LOC+14+5023949670870::9'QTY+198:0'LOC+14+5023949 670870::9'QTY+83:0'LOC+14+5023949670870::9'QTY+17: 3'LOC+14+5023949692755::9'QTY+198:0'LOC+14+5023949 692755::9'QTY+83:0'LOC+14+5023949692755::9'QTY+17: 8'LOC+14+5023949771634::9'QTY+198:0'LOC+14+5023949 771634
::9'QTY+83:0'LOC+14+5023949771634::9'QTY+17:7'LOC+ 18+5023949825700::9'QTY+17:4'LOC+14+5023949867056: :9'QTY+198:0'LOC+14+5023949867056::9'QTY+83:0'LOC+ 14+5023949867056::9'QTY+17:5'LOC+14+5023949929392: :9'QTY+198:0'LOC+14+5023949929392::9'QTY+83:0'LOC+ 14+502
3949929392::9'LIN+3++21348914:EN'QTY+17:1'LOC+14+5 023949248730::9'QTY+198:0'LOC+14+5023949248730::9' QTY+83:0'LOC+14+5023949248730::9'QTY+17:2'LOC+14+5 023949319342::9'QTY+198:0'LOC+14+5023949319342::9' QTY+83:0'LOC+14+5023949319342::9'LIN+4++21381416:E N'QTY+
17:1'LOC+14+5023949182579::9'QTY+198:0'LOC+14+5023 949182579::9'QTY+83:0'LOC+14+5023949182579::9'LIN+ 5++21481499:EN'QTY+17:1'LOC+14+5023949057895::9'QT Y+198:0'LOC+14+5023949057895::9'QTY+83:0'LOC+14+50 23949057895::9'QTY+17:1'LOC+14+5023949373414::9'QT Y+198:
0'LOC+14+5023949373414::9'QTY+83:0'LOC+14+50239493 73414::9'LIN+6++21481505:EN'QTY+17:1'LOC+14+502394 9057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY+8 3:0'LOC+14+5023949057895::9'QTY+17:2'LOC+14+502394 9771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+8 3:0'LO
C+14+5023949771634::9'LIN+7++21481512:EN'QTY+17:1' LOC+14+5023949057895::9'QTY+198:0'LOC+14+502394905 7895::9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:3' LOC+14+5023949145040::9'QTY+198:0'LOC+14+502394914 5040::9'QTY+83:0'LOC+14+5023949145040::9'QTY+17:1' LOC+14
+5023949670870::9'QTY+198:0'LOC+14+5023949670870:: 9'QTY+83:0'LOC+14+5023949670870::9'QTY+17:1'LOC+14 +5023949701028::9'QTY+198:0'LOC+14+5023949701028:: 9'QTY+83:0'LOC+14+5023949701028::9'QTY+17:1'LOC+14 +5023949771634::9'QTY+198:0'LOC+14+5023949771634:: 9'QTY+
83:0'LOC+14+5023949771634::9'QTY+17:1'LOC+14+50239 49832131::9'QTY+198:0'LOC+14+5023949832131::9'QTY+ 83:0'LOC+14+5023949832131::9'LIN+8++21481529:EN'QT Y+17:2'LOC+14+5023949223920::9'QTY+198:0'LOC+14+50 23949223920::9'QTY+83:0'LOC+14+5023949223920::9'QT Y+17:1
'LOC+14+5023949374976::9'QTY+198:0'LOC+14+50239493 74976::9'QTY+83:0'LOC+14+5023949374976::9'QTY+17:1 'LOC+14+5023949832131::9'QTY+198:0'LOC+14+50239498 32131::9'QTY+83:0'LOC+14+5023949832131::9'LIN+9++2 1493393:EN'QTY+17:1'LOC+14+5023949049625::9'QTY+19 8:0'LO
C+14+5023949049625::9'QTY+83:0'LOC+14+502394904962 5::9'LIN+10++5014838064023:EN'QTY+17:5'LOC+14+5023 949049625::9'QTY+198:0'LOC+14+5023949049625::9'QTY +83:0'LOC+14+5023949049625::9'QTY+17:5'LOC+14+5023 949057895::9'QTY+198:0'LOC+14+5023949057895::9'QTY +83:0'
LOC+14+5023949057895::9'QTY+17:5'LOC+14+5023949136 774::9'QTY+198:0'LOC+14+5023949136774::9'QTY+83:0' LOC+14+5023949136774::9'QTY+17:2'LOC+14+5023949145 040::9'QTY+198:1'LOC+14+5023949145040::9'QTY+83:0' LOC+14+5023949145040::9'QTY+17:11'LOC+14+502394918 2579::
9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+1 4+5023949182579::9'QTY+17:2'LOC+14+5023949223920:: 9'QTY+198:0'LOC+14+5023949223920::9'QTY+83:0'LOC+1 4+5023949223920::9'QTY+17:5'LOC+14+5023949232199:: 9'QTY+198:0'LOC+14+5023949232199::9'QTY+83:0'LOC+1 4+5023
949232199::9'QTY+17:5'LOC+14+5023949248730::9'QTY+ 198:0'LOC+14+5023949248730::9'QTY+83:0'LOC+14+5023 949248730::9'QTY+17:11'LOC+14+5023949294535::9'QTY +198:2'LOC+14+5023949294535::9'QTY+83:0'LOC+14+502 3949294535::9'QTY+17:5'LOC+14+5023949319342::9'QTY +198:0
'LOC+14+5023949319342::9'QTY+83:0'LOC+14+502394931 9342::9'QTY+17:4'LOC+14+5023949327619::9'QTY+198:0 'LOC+14+5023949327619::9'QTY+83:0'LOC+14+502394932 7619::9'QTY+17:5'LOC+14+5023949373414::9'QTY+198:0 'LOC+14+5023949373414::9'QTY+83:0'LOC+14+502394937 3414::
9'QTY+17:5'LOC+14+5023949374976::9'QTY+198:0'LOC+1 4+5023949374976::9'QTY+83:0'LOC+14+5023949374976:: 9'QTY+17:3'LOC+14+5023949414768::9'QTY+198:0'LOC+1 4+5023949414768::9'QTY+83:0'LOC+14+5023949414768:: 9'QTY+17:5'LOC+14+5023949423933::9'QTY+198:0'LOC+1 4+5023
949423933::9'QTY+83:0'LOC+14+5023949423933::9'QTY+ 17:4'LOC+14+5023949510183::9'QTY+198:0'LOC+14+5023 949510183::9'QTY+83:0'LOC+14+5023949510183::9'QTY+ 17:5'LOC+14+5023949511753::9'QTY+198:0'LOC+14+5023 949511753::9'QTY+83:0'LOC+14+5023949511753::9'QTY+ 17:3'L
OC+14+5023949584122::9'QTY+198:0'LOC+14+5023949584 122::9'QTY+83:0'LOC+14+5023949584122::9'QTY+17:5'L OC+14+5023949597339::9'QTY+198:0'LOC+14+5023949597 339::9'QTY+83:0'LOC+14+5023949597339::9'QTY+17:5'L OC+14+5023949670870::9'QTY+198:0'LOC+14+5023949670 870::9
'QTY+83:0'LOC+14+5023949670870::9'QTY+17:5'LOC+14+ 5023949692755::9'QTY+198:0'LOC+14+5023949692755::9 'QTY+83:0'LOC+14+5023949692755::9'QTY+17:5'LOC+14+ 5023949701028::9'QTY+198:0'LOC+14+5023949701028::9 'QTY+83:0'LOC+14+5023949701028::9'QTY+17:6'LOC+14+ 502394
9771634::9'QTY+198:0'LOC+14+5023949771634::9'QTY+8 3:0'LOC+14+5023949771634::9'QTY+17:5'LOC+14+502394 9832131::9'QTY+198:0'LOC+14+5023949832131::9'QTY+8 3:0'LOC+14+5023949832131::9'QTY+17:5'LOC+14+502394 9833970::9'QTY+198:0'LOC+14+5023949833970::9'QTY+8 3:0'LO
C+14+5023949833970::9'QTY+17:6'LOC+14+502394984330 4::9'QTY+198:0'LOC+14+5023949843304::9'QTY+83:0'LO C+14+5023949843304::9'QTY+17:1'LOC+14+502394986705 6::9'QTY+198:2'LOC+14+5023949867056::9'QTY+83:0'LO C+14+5023949867056::9'QTY+17:5'LOC+14+502394991285 9::9'Q
TY+198:0'LOC+14+5023949912859::9'QTY+83:0'LOC+14+5 023949912859::9'QTY+17:4'LOC+14+5023949929392::9'Q TY+198:0'LOC+14+5023949929392::9'QTY+83:0'LOC+14+5 023949929392::9'QTY+17:5'LOC+14+5023949955601::9'Q TY+198:0'LOC+14+5023949955601::9'QTY+83:0'LOC+14+5 023949
955601::9'QTY+17:5'LOC+14+5023949962472::9'QTY+198 :1'LOC+14+5023949962472::9'QTY+83:0'LOC+14+5023949 962472::9'LIN+11++5014838066317:EN'QTY+17:2'LOC+14 +5023949057895::9'QTY+198:0'LOC+14+5023949057895:: 9'QTY+83:0'LOC+14+5023949057895::9'QTY+17:1'LOC+14 +50239
49136774::9'QTY+198:0'LOC+14+5023949136774::9'QTY+ 83:0'LOC+14+5023949136774::9'QTY+17:1'LOC+14+50239 49182579::9'QTY+198:0'LOC+14+5023949182579::9'QTY+ 83:0'LOC+14+5023949182579::9'QTY+17:1'LOC+14+50239 49223920::9'QTY+198:0'LOC+14+5023949223920::9'QTY+ 83:0'L
OC+14+5023949223920::9'QTY+17:3'LOC+14+50239492487 30::9'QTY+198:0'LOC+14+5023949248730::9'QTY+83:0'L OC+14+5023949248730::9'QTY+17:3'LOC+14+50239493734 14::9'QTY+198:0'LOC+14+5023949373414::9'QTY+83:0'L OC+14+5023949373414::9'QTY+17:1'LOC+14+50239493749 76::9'
QTY+198:0'LOC+14+5023949374976::9'QTY+83:0'LOC+14+ 5023949374976::9'QTY+17:1'LOC+14+5023949423933::9' QTY+198:0'LOC+14+5023949423933::9'QTY+83:0'LOC+14+ 5023949423933::9'QTY+17:3'LOC+14+5023949511753::9' QTY+198:0'LOC+14+5023949511753::9'QTY+83:0'LOC+14+ 502394
9511753::9'QTY+17:2'LOC+14+5023949584122::9'QTY+19 8:0'LOC+14+5023949584122::9'QTY+83:0'LOC+14+502394 9584122::9'QTY+17:1'LOC+14+5023949597339::9'QTY+19 8:0'LOC+14+5023949597339::9'QTY+83:0'LOC+14+502394 9597339::9'QTY+17:4'LOC+14+5023949764661::9'QTY+19 8:0'LO
C+14+5023949764661::9'QTY+83:0'LOC+14+502394976466 1::9'QTY+17:3'LOC+14+5023949771634::9'QTY+198:0'LO C+14+5023949771634::9'QTY+83:0'LOC+14+502394977163 4::9'QTY+17:1'LOC+14+5023949833970::9'QTY+198:0'LO C+14+5023949833970::9'QTY+83:0'LOC+14+502394983397 0::9'Q
TY+17:1'LOC+14+5023949867056::9'QTY+198:0'LOC+14+5 023949867056::9'QTY+83:0'LOC+14+5023949867056::9'Q TY+17:1'LOC+14+5023949929392::9'QTY+198:0'LOC+14+5 023949929392::9'QTY+83:0'LOC+14+5023949929392::9'Q TY+17:1'LOC+14+5023949962472::9'QTY+198:0'LOC+14+5 023949
962472::9'QTY+83:0'LOC+14+5023949962472::9'LIN+129 ++5014838370384:EN'QTY+17:1'LOC+14+5023949182579:: 9'QTY+198:0'LOC+14+5023949182579::9'QTY+83:0'LOC+1 4+5023949182579::9'QTY+17:2'LOC+14+5023949670870:: 9'QTY+198:0'LOC+14+5023949670870::9'QTY+83:1'LOC+1 4+5023949670870::9'QTY+17:2'LOC+1
4+5023949867056::9'QTY+198:0'LOC+14+5023949867056: :9'QTY+83:0'LOC+14+5023949867056::9'LIN+130++50148 38370414:EN'QTY+17:2'LOC+14+5023949145040::9'QTY+1 98:0'LOC+14+5023949145040::9'QTY+83:0'LOC+14+50239 49145040::9'QTY+17:1'LOC+14+5023949327619::9'QTY+1 98:0'L
OC+14+5023949327619::9'QTY+83:0'LOC+14+50239493276 19::9'QTY+17:1'LOC+14+5023949584122::9'QTY+198:0'L OC+14+5023949584122::9'QTY+83:0'LOC+14+50239495841 22::9'QTY+17:2'LOC+14+5023949670870::9'QTY+198:0'L OC+14+5023949670870::9'QTY+83:0'LOC+14+50239496708 70::9'
QTY+17:1'LOC+14+5023949929392::9'QTY+198:0'LOC+14+ 5023949929392::9'QTY+83:0'LOC+14+5023949929392::9' LIN+131++5014838370438:EN'QTY+17:0'LOC+14+50239493 19342::9'QTY+198:0'LOC+14+5023949319342::9'QTY+83: 2'LOC+14+5023949319342::9'UNT+15237+23'UNZ+1+436'