Help with .csv file

[DJL]

Hi All

I am after a bit of advice. I am not familiar with .CSV files but I have one that I have to write some code on.

There is a date field which if I open manually shows 03 Aug 2009 which is correct... however if I open the same file through my code using workbooks.Open Filename:= "etc...."
the date is showing as 08 Mar 2009.

Does anybody know why this might happen and how it can be resolved. I have checked with our IT guys and the report can only be exported on .csv format.

Thanks
Donna

 

[Weaver]

Sounds similar to a problem I was having.

Use the macro recorder and open the .csv by using the 'import external data' option. When you do this, there is a point where you can set the format of any date fields. At this point, you can choose dmy from a drop down list which should fix the problem.

HTH

 

[Richard Schollar]

Hi Donna

Can you show the line of code you are using to open this file and the version of Excel you are using (eg xl2003 or 2007)?

 

[DJL]

Weaver - Thank you so much, problem solved, that was really giving me a headache.

Richard - I have solved my problem using the text import tool but am always eager to learn other code so I have pasted my code in case you have an alternative solution.

Workbooks.Open Filename:= _
"G:\Adviser Services\TCF\Templates\NewBusinessByAdviser.xlsx"

Thank you both

Donna

 

[DJL]

Sorry that should have been .csv not .xlsx

 

[Weaver]

Weaver - Thank you so much, problem solved, that was really giving me a headache.

As I remember, it gave me quite a headache at the time too!

 

[Richard Schollar]

Donna

If you use the OpenText method then you can specify the local parameter to be True 9which means it will use your Regional Settings instead of VBA's US-centric view):

Workbooks.OpenText Filename:= _
"G:\Adviser Services\TCF\Templates\NewBusinessByAdviser.csv", Local:=True

 

[Weaver]

That's good to know, Richard.

 

[DJL]

This is great too. Thank you so much.

Donna