ok I use the following code to open an textfile and strip some infomation and paste it into my spreadsheet
VB Code:
Private Sub CommandButton1_Click() MyPath = "C:\chris\temp\ordercode.ali" Open MyPath For Input As #1 ReDim codes(150) ReDim quant(150) temp = "" Do Until temp = "S-O-O" Or EOF(1) Input #1, temp Loop x = 1 Input #1, dates Input #1, temp Do Until temp = "E-O-O" Or EOF(1) codes(x) = temp Input #1, temp quant(x) = temp Input #1, temp x = x + 1 Loop Top: x = 1 Do While codes(x) <> "" And quant(x) <> "" Set curcell = Worksheets(1).Cells(x + 1, 3) curcell.Value = codes(x) Set curcell = Worksheets(1).Cells(x + 1, 4) curcell.Value = quant(x) x = x + 1 Loop dates = Left(dates, 3) thecode(1) = Asc(Left(dates, 1)) ' - 1935) thecode(1) = thecode(1) + 1935 thecode(2) = Asc(Mid(dates, 2)) ' + 67) thecode(2) = thecode(2) - 67 thecode(3) = Asc(Right(dates, 1)) ' < 90 Then thecode(3) = thecode(3) - 64 If thecode(3) > 26 Then thecode(3) = thecode(3) - 96 End If newdate = thecode(3) & "/" & thecode(2) & "/" & thecode(1) newdate = Format(newdate, "dd/mmm/yyyy") Range("a2") = newdate Close 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
