Date Format Changing when VBA opens a file

[mcfclax]

Hi

If I manually open a CSV file all of the dates appear in the UK format DD/MM/YYYY. When VBA opens the exact same file the way it interprets the dates is in the US format MM/DD/YYYY. So the date 09/02/2015 changes from 9th Feb to the 2nd Sep. And the 26th Feb becomes a text field.

I am using the below very basic command to open the file and even with just this code it opens incorrectly.

Workbooks.Open Filename:="H:\Account Management\Admin\Stats\1.csv"

I've checked the system short date etc in the control panel and that shows as DD/MM/YYYY so I'm stuck as to why this is happening.

Any suggestions for either how to fix it or a work around?

Thanks

Tom

 

[healey21]

What if you also check the local setting something like this

Workbooks.Open Filename:="H:\Account Management\Admin\Stats\1.csv", Local:=True

 

[steve the fish]

Or save the file as a .txt file to force excel to use the text import wizard on open. Play around with the date settings for your column there.

 

[mcfclax]

What if you also check the local setting something like this

Thanks for this Healey21, it works perfectly. I never knew you could dictate if local or other settings are used when opening a file.

For the life of me I can't think of a situation when you'd want to specify to use non local settings though.

 

[healey21]

Happy to help and thank you for letting me know you have a solution.