Solved

Strange VBA Conundrum......

Posted on 2013-05-22
17
974 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 23

Expert Comment

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

Assisted Solution

by:gnazareth
gnazareth earned 50 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
 

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 23

Assisted Solution

by:Eirman
Eirman earned 50 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now