[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Strange VBA Conundrum......

Posted on 2013-05-22
17
Medium Priority
?
1,027 Views
Last Modified: 2013-05-22
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
Comment
Question by:David Phelops
  • 9
  • 3
  • 2
  • +2
17 Comments
 
LVL 24

Expert Comment

by:Eirman
ID: 39187435
What is your regional settings for dates ?
0
 
LVL 3

Assisted Solution

by:gnazareth
gnazareth earned 200 total points
ID: 39187442
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
 

Author Comment

by:David Phelops
ID: 39187459
@eirman - Regional Date settings for pc are Short Date: dd/mm/yyyy
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:David Phelops
ID: 39187465
But, why would it be different when opened by code, or by explorer.  That is the conundrum...???
0
 
LVL 24

Assisted Solution

by:Eirman
Eirman earned 200 total points
ID: 39187468
0
 

Author Comment

by:David Phelops
ID: 39187534
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
 

Expert Comment

by:CABGx3
ID: 39187625
Check your file to insure all dates are dates and not TEXT.

Vaya con Dios,
Chuck, CABGx3
0
 

Author Comment

by:David Phelops
ID: 39187652
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1600 total points
ID: 39187682
Try using:

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

Open in new window


VBA is very US-centric!
0
 

Author Comment

by:David Phelops
ID: 39187720
Thankyou rorya - simple and effective as usual.... That works... Hooray for LOCAL = TRUE
Cheers!

David
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39187732
simple and effective as usual

I've been called far worse. :)
0
 

Author Closing Comment

by:David Phelops
ID: 39187737
Simple, effective. Worked.
0
 

Author Comment

by:David Phelops
ID: 39187745
We all have..... :-)
0
 

Expert Comment

by:CABGx3
ID: 39187957
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
 

Author Comment

by:David Phelops
ID: 39187969
Thanks Chuck.... What can I say... Appreciate your help too.
Hope I can return the favour one day... (wry laugh!)
Cheers
David
0
 

Expert Comment

by:CABGx3
ID: 39187992
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
 

Author Comment

by:David Phelops
ID: 39188016
Don't see much danger of staying away.  Always good to connect with the world out there.
David
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question