Date format problem when open csv file using macro

[wallstudio]

I have thousands of csv files in a folder. Column A is the date in ascending order. I am trying to open one of them with macro and vlookup rows of date from the csv file and return data to the workbook.

My computer use UK format for date.

The problem is whenever I run the macro, the date format on the csv file are messed up. Some of them display as US format, and some of them display as UK format.

This is a portion of the way they appear:
23/02/2011
24/02/2011
25/02/2011
28/02/2011
03/01/2011
03/02/2011
03/03/2011
03/04/2011
03/07/2011
03/08/2011
03/09/2011
03/10/2011
03/11/2011
14/03/2011
15/03/2011
16/03/2011
17/03/2011

However, there will be no problem if I click and open the same file directly.

The macro is :

Sub OpenFile()
Workbooks.Open Filename:=Range("A13") & Range("A12") & ".csv"
ThisWorkbook.Activate
End Sub​

A13 is the path and A12 is the name of the file which needed to be opened.

Anyone know what's wrong? Thanks.

 

[John_w]

Try:

Workbooks.Open Filename:=Range("A13") & Range("A12") & ".csv", Local:=True

to preserve the UK dates.

 

[wallstudio]

It works. Thanks.

 

[UKNN]

Genius! This same problem was driving me mad, Many thanks!