Date format problem when using macro

[jt42cwr]

Hi,

I am having a little trouble using a macro when copying some dates in that it mixes up UK and US date formats.

I have a .csv file in which in one column there are some date values in the UK format "dd/mm/yyyy", such as 10/08/2010 for 10th August 2010. Now, when I open this .csv file, and manually copy & paste values this column to my Excel workbook with the column already formatted to the UK date format it works fine. If however I do this using a macro it converts the cells format in the Excel workbook to *14/03/2001 and the date will now read 08/10/2010 instead of 10/08/2010.

Can anyone help with this?

'Copy dates
Windows("LPS load orders data.csv").Activate
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Windows("OMS booking time generator.xls").Activate
Sheets("Links").Select
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

 

[Derek Brown]

Annoying, isn't it?
I had the same problem with a .csv file and overcame the problem by opening the file in the following way:

Set wbInput = Workbooks.Open(oFileItem, Local:=True)

I believe that the 'Local' argument causes the PC date format to be used.
In this example, oFileItem is an object assigned to the .csv file but could have been something like "C:\LPS load orders data.csv"