• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1033
  • Last Modified:

Strange VBA Conundrum......

Experts, I have a problem that is driving me cross-eyed.

I have a csv file called TN0001.csv (attached).
Column A is "Date".  Format is dd/mm/yyyy
Date range is 14/04/2013  -  12/05/2013

When I open the file from explorer in the normal way, it is absolutely fine.
All the dates are in the correct format

HOWEVER, when the file is opened from within a routine:
 Workbooks.Open FileName:="C:\TN0000"

Open in new window

the formatting of the dates is inconsistent. I.E.
All the April dates are correctly formatted: 14/04/2013
BUT, all the May dates are the wrong way round. I.E. 05/12/2013 instead of 12/05/2013.

I have tried changing the directory.  I have tried runnning it on different computers.
I cannot for the life of me fathom out why number date formats should change because a file is being opened with code, instead of a mouse!

I have attached a separate file showing the different formats.

Please, Please, can someone help?

Thanks
David
TN0001.csv
Comparison-Date-Formats.csv
0
David Phelops
Asked:
David Phelops
  • 9
  • 3
  • 2
  • +2
3 Solutions
 
EirmanChief Operations ManagerCommented:
What is your regional settings for dates ?
0
 
gnazarethCommented:
It has something to do with the date format. Your computer is probably set to mm/dd/yy, while you csv is set to dd/mm/yy.
14/04/2013 works ok, because 14 is not a month, so Excel uses dd/mm/yy for that. You will have issues where the day is 12 or less.
0
 
David PhelopsAuthor Commented:
@eirman - Regional Date settings for pc are Short Date: dd/mm/yyyy
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
David PhelopsAuthor Commented:
But, why would it be different when opened by code, or by explorer.  That is the conundrum...???
0
 
David PhelopsAuthor Commented:
Thanks for the articles.... I have had a look at them all. They are helpful explaining how excel works with dates.  
My settings are English (attached screenshots).

None of these articles explain how opening a csv file might differ using VBA code, rather than using explorer.... i cannot get my head round that particular brain-teaer.
PC-Settings.docx
0
 
CABGx3Commented:
Check your file to insure all dates are dates and not TEXT.

Vaya con Dios,
Chuck, CABGx3
0
 
David PhelopsAuthor Commented:
Thank you... all dates are dates... When i opened the file with explorer, I tried formatting the "date" column as "General" - this returned a number for each row of data.  From this, i deduced that each date is a date and not text.  Furthermore, when I saved the file again after changing the number format, it opened up correctly using VBA....

I am on the verge of returning to paper and pen, quill, papyrus, chisel.... :-)
0
 
Rory ArchibaldCommented:
Try using:

Workbooks.Open FileName:="C:\TN0000", Local:=True

Open in new window


VBA is very US-centric!
0
 
David PhelopsAuthor Commented:
Thankyou rorya - simple and effective as usual.... That works... Hooray for LOCAL = TRUE
Cheers!

David
0
 
Rory ArchibaldCommented:
simple and effective as usual

I've been called far worse. :)
0
 
David PhelopsAuthor Commented:
Simple, effective. Worked.
0
 
David PhelopsAuthor Commented:
We all have..... :-)
0
 
CABGx3Commented:
David said.....Thank you... all dates are dates... When i opened the file with explorer, I tried formatting the "date" column as "General" - this returned a number for each row of data.  From this, i deduced that each date is a date and not text.  Furthermore, when I saved the file again after changing the number format, it opened up correctly using VBA....

That's strange too.....the file I downloaded and opened in Excel (both 2002 and 2010) showed all the April dates as TEXT and the May dates as dates.

Oh well, the important thing is that you got it working.

Vaya con Dios,
Chuck, CABGx3
0
 
David PhelopsAuthor Commented:
Thanks Chuck.... What can I say... Appreciate your help too.
Hope I can return the favour one day... (wry laugh!)
Cheers
David
0
 
CABGx3Commented:
You're welcome David, and you already have returned the favor, for "asking questions is just as important as answering them".  Speaking personally, my Excel/VBA abilities are very volitile, and if left unused they tend to fade.  I appreciate every opportunity to help someone else for it keeps me going too.

Keep coming back!

Vaya con Dios,
Chuck, CABGx3
0
 
David PhelopsAuthor Commented:
Don't see much danger of staying away.  Always good to connect with the world out there.
David
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now