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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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

ADVERTISEMENT

Sorry that should have been .csv not .xlsx
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,485
Messages
5,831,965
Members
430,098
Latest member
bemmelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top