Solved

Strange VBA Conundrum......

Posted on 2013-05-22
17
966 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
Comment Utility
What is your regional settings for dates ?
0
 
LVL 3

Assisted Solution

by:gnazareth
gnazareth earned 50 total points
Comment Utility
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
Comment Utility
@eirman - Regional Date settings for pc are Short Date: dd/mm/yyyy
0
 

Author Comment

by:David Phelops
Comment Utility
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
Comment Utility
0
 

Author Comment

by:David Phelops
Comment Utility
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
Comment Utility
Check your file to insure all dates are dates and not TEXT.

Vaya con Dios,
Chuck, CABGx3
0
 

Author Comment

by:David Phelops
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
Comment Utility
Try using:

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

Open in new window


VBA is very US-centric!
0
 

Author Comment

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

David
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
simple and effective as usual

I've been called far worse. :)
0
 

Author Closing Comment

by:David Phelops
Comment Utility
Simple, effective. Worked.
0
 

Author Comment

by:David Phelops
Comment Utility
We all have..... :-)
0
 

Expert Comment

by:CABGx3
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Don't see much danger of staying away.  Always good to connect with the world out there.
David
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

762 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

11 Experts available now in Live!

Get 1:1 Help Now