Date format error with CSV file


F

FmEdit

Hi,

Using Excel 2007

I have a csv file that when opened manually has the correct date
format of DD/MM/YYYY

When I open the file with VBA, the date format defaults to MM/DD/YYYY,
some of the dates come through as text fields.

Even importing this through VBA as a text file (with .csv extension)
and changing the format to D/M/Y it still comes in as MM/DD/YYYY and
some of the fields being text, when I change the extension to ".txt"
it imports OK

I do not want to have my code renaming the files to .txt in order to
get the correct date format

Any help on this would be greatly appreciated

Regards
Raymond Allan
 
Ad

Advertisements

A

AP

Hi,

Using Excel 2007

I have a csv file that when opened manually has the correct date
format of DD/MM/YYYY

When I open the file with VBA, the date format defaults to MM/DD/YYYY,
some of the dates come through as text fields.

Even importing this through VBA as a text file (with .csv extension)
and changing the format to D/M/Y it still comes in as MM/DD/YYYY and
some of the fields being text, when I change the extension to ".txt"
it imports OK

I do not want to have my code renaming the files to .txt in order to
get the correct date format

Any help on this would be greatly appreciated

Regards
Raymond Allan

Is this something you can fix by making the appropriate adjustment in
your Regional Settings under Control Panel ?

Otherwise you might have to manipulate all the date values in your VBA
code.

AP
 
F

FmEdit

Is this something you can fix by making the appropriate adjustment in
your Regional Settings under Control Panel ?

Otherwise you might have to manipulate all the date values in your VBA
code.

AP- Hide quoted text -

- Show quoted text -

My regional settings are all set to UK format (default)

The frustrating part is when it is manually opened, all the date
fields are in the correct format, by using VBA to open the file it
defaults to the US format and any date that has the day > 12 becomes a
text field

RA
 
B

BlackSun

Il 28/01/2010 15.20, FmEdit ha scritto:
My regional settings are all set to UK format (default)

The frustrating part is when it is manually opened, all the date
fields are in the correct format, by using VBA to open the file it
defaults to the US format and any date that has the day> 12 becomes a
text field

RA

Menu Data - Text to Column (I think so in english version)
 
F

FmEdit

Il 28/01/2010 15.20, FmEdit ha scritto:









Menu Data - Text to Column (I think so in english version)

The text to column is my current workaround, I was hoping for a
solution that would not invlove having to manipulate the data.

RA
 
M

michdenis

Hi,

Try this :

Workooks.Open Filename:="c:\Excel\MyFile.csv",Local:=True



"FmEdit" <[email protected]> a écrit dans le message de groupe de discussion
: (e-mail address removed)...
Hi,

Using Excel 2007

I have a csv file that when opened manually has the correct date
format of DD/MM/YYYY

When I open the file with VBA, the date format defaults to MM/DD/YYYY,
some of the dates come through as text fields.

Even importing this through VBA as a text file (with .csv extension)
and changing the format to D/M/Y it still comes in as MM/DD/YYYY and
some of the fields being text, when I change the extension to ".txt"
it imports OK

I do not want to have my code renaming the files to .txt in order to
get the correct date format

Any help on this would be greatly appreciated

Regards
Raymond Allan
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top