Excel Date re-formatting

Posted by Des Feeley on August 12, 1999 6:04 AM

Hi,
perhaps this is not your thing but it is doing my head in, so to speak

I have a one line excel 97 vba macro, shown below ...

Sub main()
Workbooks.Open FileName:="datetrial.csv"
End Sub

The file "datetrial.csv" contains one line!!!

Date 1,05/08/1999

I'm in Europe, so it's the fifth of August to me.

Okay here is the question ... If I run the one line macro, to open the one line file, direct
from the excel vba window (i.e. F5 or the 'Play' button), then it works fine, 05/08/1999 in cell B1

If I create a button on the excel toolbar, and link this button to the macro ("main") when it
opens the file, it reverses the date format!!! As in it inputs the date as 08/05/1999. The format of the cell is set to Custom dd/mm/yyyy, which makes no sense at all.

Something different is happening when I run line of code from the Button toolbar.

any thoughts?

(I have come across something similar in the Microsoft knowledge base, but they have no resolution suggested.
Article Q172588)

Posted by Ivan F Moala on August 13, 1999 3:27 AM

Does your macro reformat the value to your date
format ??
If not then you could try doing this as a work around.

Ivan

Posted by Des Feeley on August 16, 1999 2:05 AM

Ivan

Well ... the macro doesn't attempt to do any formatting at all, just to open the file. Excel attempts to interpret what each column of data is and is making a mess of it.

I have actually found a workaround, where if I call the file a text-file, and specify explicitly the delimiter and which columns of data are to be taken in as text; then it sort of works. Only sort of, because I have to specify almost every column's format, (as opposed to the one of fifty that I am interested in), or else it does its best to make a hash of it.

Darned annoying ... but there you go.

thanks,

Posted by Ivan Moala on August 17, 1999 3:57 AM

Sorry Des
What I meant was that if your macro didn't format
your data to the date format you required then to
try and amend your macro TO FORMAT it to the requied
format.

Ivan