Opening .CSV file

[Paul W Smith]

I have a .CSV file which i am opening through VBA code:

set wb = workbooks.open(Filepath)

When I open the file the first columns reads:

19/04/10
19/04/10
19/04/10

When I open the file via the file menu (File | Open) the first column reads:

19/04/2010
19/04/2010
19/04/2010

What do I have to do to be able to open the file via VBA and have the column
1 values seen as they appear when the file is opened normally?

PWS

 

[Dave Peterson]

When you use File|Open, excel will use your windows regional settings -- and you
have your date format set to dmy.

But when VBA opens the file, it doesn't use your regional setting. It's USA
centric and uses an mdy order.

And since 19/04/10 doesn't look like a date in mdy order, excel will treat it as
text.

You may have noticed that some data comes in ok. But it's not!

It may look like it's a date, but it's not the date that the CSV file wants.

05/06/10 would come in as May 6, 2010 -- not June 6, 2010 (what the data really
meant).

So don't just try to fix the data after importing it.

Instead, rename/copy the .CSV file to .TXT. And then record a macro when you
open that file manually. Excel will show you the text to columns wizard and
you'll be able to specify that field as a date (dmy order).

Then incorporate that recorded code into your existing macro.

But you'll have to rename/copy that CSV file (either manually or via code)
before this modified code runs.

 

[Paul W Smith]

Or I could just add the Local= TRUE :

set wb = workbooks.open(FileName:=Filepath, Local:=True)

I answer my own question in case away seaches my original question and wants
another solution.

 

[Dave Peterson]

That'll depend on what versions of excel you're supporting.