Sleeper: Date format during .csv import

[Valex]

Hi



I somehow managed to import all csv files from one directory to a single worksheet. Unfortunately in the csv files the date is in format

06/01/2005,..

Using the command: "sourceRange.Copy destrange" inside the macro, Excel change the format

01/11/2005

01/11/2005

13/01/2005

13/01/2005


in the point were 12 become 13 because of the months number.

I will appreciate any advice.

Thanks

[Ken Puls]

Hi Valex,

I don't know if it's too late to go this route for you... Last time I had this issue, I renamed the csv file to a txt extenstion, then imported it to Excel. Excel launches the import wizard at that point, so you can force it to read the dates in a specific order. (Third or fourth tab in the wizard)

HTH,

[Valex]

Thanks Ken,
Thanks for the replay.
Sorry is my fault. The word "import" I have used above is more explanation then exact command.
The problem appears during copy range from csv file and paste it ito exel file.

Regards

[Ken Puls]

Hi Valex,

Fair enough, but actually what I said still holds true. Excel thinks it's pretty smart when opening CSV files, and makes some assumptions about dates. Changing the extension of the file to txt, and then running the real import wizard gives you control over the interpretation where csv does not.

Try making a copy of the csv, changing the extension to txt, and opening it in Excel. You should see what I mean. I suspect that the dates will come across okay, but it might depend on how much tinkering Excel has already done with them. At any rate, it won't hurt to try it out, and it may save a ton of work to correct it in another way.

Lest you're worried about the amount of work to import through the wizard, it can be coded with VBA to be automatically imported in the correct format. (Provided the columns are always consistent)

Let me know,

[Valex]

Thanks Ken,

Files are in. Via txt import procedure as in your suggestion.

Unfortunately sorting by date , have a result is actually sorting by day. Seems I have to discover the wheel, adding tree columns for year mm,dd to make the correct sorting rule.

What I cant understand is, why every one touching the problem have to start from zero.

It is obviously part of the game. :-)