VB converting UK to US dates, randomly, ignores cdate func!

[PBcorn]

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

 

[Jacob Skaria]

Try out this method for CSV import

--In 2007 from menu Data>From Text and in 2003 from menu Data>Import
External Data>Import Data ---select the .csv file to be opened.
--From the 'Text Import Wizard' Step 1; hit Next>Next 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.

 

[PBcorn]

Thanks,

However the macro also picks the correct csv file from a folder (there are
more than one for each day due to more than one run number - macro selects
the one corresponding to date entered by user and the latest run number).
Looks like the import process adds another layer of complexity I was hoping
to avoid. I was looking at the possibility of getting xls's instead of csv's
at source as this would be the most efficient solution but if you have any
code efficient way of doing the above with import process please let me know.

Thanks again

pb

 

[Jacob Skaria]

The below macro will import a csvfile having the second field as date in DMY
format to excel. If date is in 3rd column you can mention the array as

Array(1, 1, xlDMYFormat, 1)

Sub Macro2()
Dim wb As Workbook,strFile As string

strFile = "D:\new.csv"
Set wb = Workbooks.Add
With wb.Sheets(1).QueryTables.Add(Connection:="TEXT;" & strFile, _
Destination:=wb.Sheets(1).Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(1, xlDMYFormat, 1)
.Refresh
End With
End Sub