Macro CSV Import Doesn't Format Dates

[Igwiz]

Hi All,

I have a CSV files which has a date field in it.
I recorded a macro of opening it and it everything was fine. However, when I play the macro, the date field transforms itself into American formatting and so I get a mix of Dates and text.

What on earth is going on? Very odd as I have only recorded this and not written it!

Cheers,
Ig

 

[Igwiz]

Any help would be appreciated. Many thanks!

 

[RamziSaab]

if u know any vb coding u can manually change the code so that it format that column in the date format u want...

I am guessing the macro changes the date format...just step into the macro and find where it changes the date format and use this line instead

Selection.NumberFormat = "dd/mm/yy"

hope it works...otherwise u might have to copy and paste the code so we can see where it went wrong!"

 

[Igwiz]

The macro is simply the line:

workbooks.open filename = "test.csv"

That's the point - nothing clever is going on at all. I've looked at my regional settings but no joy. Like I said, what is infuriating is that if I open the file from the file menu it loads fine. It's only when it loads via the above line that the dates screw up!!

 

[Igwiz]

The macro is simply the line:

workbooks.open filename = "test.csv"

That's the point - nothing clever is going on at all. I've looked at my regional settings but no joy. Like I said, what is infuriating is that if I open the file from the file menu it loads fine. It's only when it loads via the above line that the dates screw up!!

 

[RamziSaab]

what format are they in the CSV file?

 

[Igwiz]

English dd/mm/yy

 

[RamziSaab]

i duuno but i would just add a code at the end to make sure it formats it to english...sorry maybe someone else has a better solution

 

[DavidHerron]

Igwiz,

I too have encountered the EXACT same problem. I have yet to find a solution. It is only when you use the workbooks.open or workbooks.opentext commands that the date is converted to american date. Clicking on file > open from the menu does not cause the error.

It is VERY frustrating..

Has anyone else experienced this issue?

Dave

 

[cheerio]

The simplest solution to this is to replace the / with a comma so that your dates are formatted as dd,mm,yy.

Now the csv import will bring in the three as separate fields. Finally use an Excel function to recombine back into a date.