ok I use the following code to open an textfile and strip some infomation and paste it into my spreadsheet

VB Code:
  1. Private Sub CommandButton1_Click()

  2.  
  3. MyPath = "C:\chris\temp\ordercode.ali"

  4. Open MyPath For Input As #1

  5. ReDim codes(150)

  6. ReDim quant(150)

  7. temp = ""

  8. Do Until temp = "S-O-O" Or EOF(1)

  9. Input #1, temp

  10. Loop

  11.  
  12. x = 1

  13. Input #1, dates

  14. Input #1, temp

  15. Do Until temp = "E-O-O" Or EOF(1)

  16. codes(x) = temp

  17. Input #1, temp

  18. quant(x) = temp

  19. Input #1, temp

  20. x = x + 1

  21. Loop

  22.  
  23. Top:

  24. x = 1

  25. Do While codes(x) <> "" And quant(x) <> ""

  26. Set curcell = Worksheets(1).Cells(x + 1, 3)

  27. curcell.Value = codes(x)

  28. Set curcell = Worksheets(1).Cells(x + 1, 4)

  29. curcell.Value = quant(x)

  30. x = x + 1

  31. Loop

  32.  
  33. dates = Left(dates, 3)

  34.  
  35. thecode(1) = Asc(Left(dates, 1)) ' - 1935)

  36. thecode(1) = thecode(1) + 1935

  37.  
  38. thecode(2) = Asc(Mid(dates, 2)) ' + 67)

  39. thecode(2) = thecode(2) - 67

  40.  
  41.  
  42. thecode(3) = Asc(Right(dates, 1)) ' < 90 Then

  43. thecode(3) = thecode(3) - 64

  44.     

  45.     If thecode(3) > 26 Then

  46.         thecode(3) = thecode(3) - 96

  47.     End If

  48.  
  49. newdate = thecode(3) & "/" & thecode(2) & "/" & thecode(1)

  50. newdate = Format(newdate, "dd/mmm/yyyy")

  51. Range("a2") = newdate

  52.  
  53. Close

  54.  
  55. End Sub

aslong as the text file looks like the below it works fine no problems it finds the start and end triggers and works cool. if however I have a text file just containing the start trigger (S-O-O) onward it bombs out with input past end of file....Help anyone why will it work with a large textfile but not a small one

Please respond to Marise COLQUHOUN/CS/HTLUK@HTLUK

Subject: Stationery order from Maureen Joyce on the 15/10/2002
11:29:06

Hi Susan,
Please find below the stationery order from Maureen Joyce. Please can you
check this order before forwarding this mail on to your Admin POC
Thank you

Special Instructions:

SECTION: Training & Recruitment
NAME: Maureen Joyce
COST CENTRE: CC51
CONTACT NO: 07980253199
TEAM NO: T&r
NO' OF STAFF: 20
DATE: 15/Oct/2002
REF NO': 15413
JUSTIFICATION: For the (N.I.L) Optrions Scissors P264 : Needed for
preparing training material.



*********STATIONERY ORDER**********

A1 Flipchart Pad
2108043
20 £21.80
A4 Memo Pad (5's)
3860037
24 £25.20
Clear Punched Pockets (BOX)
7300260
4 £3.64
Flip Chart Pens / Dry Wipe Pens (4's)
UDRYASS
8 £5.92

Total order value £75.02p

Please do not remove these lines of text, these are required
S-O-O,CMOXEVEWZ,2108043,20,3860037,24,7300260,4,UDRYASS,8,7154714,8,1275193,5
2965975,3,430MBL,4,2873106,3,1490870,1,4300146,4,E-O-O