View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Chris Mahoney Chris Mahoney is offline
external usenet poster
 
Posts: 8
Default Reading dates from text file

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