Split was added in xl2k. Tom Ogilvy posted a split97 that could make your life
a bit easier:

Option Explicit
Sub testme()
Dim ID As String
Dim DateVar As String
Dim myDate As Date
Dim Name As String
Dim CodeNo As String
Dim mySplit As Variant

Open "import.txt" For Input As #1
'Open "c:\my documents\excel\edit2.txt" For Input As #1
Do Until EOF(1)
Input #1, ID
Input #1, DateVar
Input #1, Name
Input #1, CodeNo

mySplit = Split97(DateVar, "/")

If UBound(mySplit) - LBound(mySplit) + 1 < 3 Then
'not a date, what should be done?
Else
'maybe some more validation????
myDate = DateSerial(mySplit(UBound(mySplit)), _
mySplit(LBound(mySplit) + 1), _
mySplit(LBound(mySplit)))
MsgBox myDate
End If
'Data processing code here
Loop
Close #1

End Sub

'from Tom Ogilvy
Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function




Chris Mahoney wrote:

OK, I'll try to reply to all these comments in one go.

I am not following your code. Input reads all the way up to the new line. So
how are you parsing the individual lines? For a CSV file (which is
essentially what you have here) you should look at using the split function.

Input is reading up to each comma, it's not reading the entire line. I can't
find any information on the Split function (I typed Split into the VBA code
and pressed Help but it only gives me a seemingly-unrelated Split property).

I expect the problem is the date format.

If the dates are read in as strings, then converted with CDate (see below),
VBA will TRY to interpret a date as mm/dd/yyyy whenever possible. If not
possible, it will try dd/mm/yyyy.

The following works for me and interprets his 2 dates correctly. However, I
added a line with the date 1/12/2004, which he would intend to be Dec 1. CDate
takes it as Jan 12.

The conclusion seems to be that the dates must be read in as text, then
manually converted to the appropriate date.

It won't interpret either of those examples properly (eg. If I add MsgBox
DateVar where the comment is, then running the code gives me 13 and 27.

What country are you in (and therefore what international settings are you
using)? I'm in New Zealand and therefore everything defaults to
Day/Month/Year. In my International settings, I have the date separator set
to / (like it is in the text file). I have also tried it with "Leading zero
for month" both off and on, same results each time.

It sure looks like input# sees that as some sort of special character.

I would either read the whole line in with "line input #" and parse it myself
or
change the program that creates the text file to drop the slashes and then
convert it to a date in the code.

I'd forgotten about Line Input, I'll give it a go and see what happens.
Changing the text file is not really an option as I don't have the app that
created it, and if I tried to write some sort of converter I'd probably run
into the same problems I'm having now.

I'll try Line Input and let you know how I get on.

Thanks
Chris

--

Dave Peterson