Try out this method for CSV import

--In 2007 from menu DataFrom Text and in 2003 from menu DataImport
External DataImport Data ---select the .csv file to be opened.
--From the 'Text Import Wizard' Step 1; hit NextNext will take you to Step
3 of 3 of the Wizard.

--From 'Column Data format' select 'Date' and select the date format in which
your data is ('MDY' or 'DMY' or what ever it is).

--Hit Finish. MSExcel will now convert the dates to the default date format
of your computer.

--
Jacob (MVP - Excel)


"PBcorn" wrote:

Written macro which processes csv files - splits into +ve & -ve values of one
colum, sorts, adda couple of calculated columns. One of the cols (col index
5) is a date: dd/mm/yyyy, however excel is randomly converting some of these
to american format. Using CDATE fuction makes no difference. The last step in
the macro is the following code to attempt to correct this:
-----------------------------------------------
with workbooks(workbookobjectname)

For Each w In .Worksheets

For i = 2 To w.UsedRange.Rows.Count
w.Cells(i, 5) = CDate(DateSerial(Year(w.Cells(i, 5)), Month(w.Cells(i,
5)), Day(w.Cells(i, 5))))
Next i

Next w

end with

-------------------------------

This does not work - why? Help appreciated.

Thanks