Help with .csv file

DJL

New Member
Joined
Feb 5, 2006
Messages
35
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Feb 5, 2006
Messages
35
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

New Member
Joined
Feb 5, 2006
Messages
35
Sorry that should have been .csv not .xlsx
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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):

Rich (BB code):
Workbooks.OpenText Filename:= _
"G:\Adviser Services\TCF\Templates\NewBusinessByAdviser.csv", Local:=True
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,840
Messages
5,507,607
Members
408,640
Latest member
billigee

This Week's Hot Topics

Top