Excel 97 customised toolbar, UK date format errors .. ?
Posted by Aid Burrow on April 10, 2001 7:14 AM
Help!
I am running Excel 97 [ver SR-2(k)] on Windows 98 SE.
UK short date in Regional Settings is dd/MM/yyyy.
I am importing a CSV file into Excel where date is in format "dd/mm/yy", ie: 12th April 2001 = "12/04/01"
I have recorded / modified a macro which (amongst other things) formats the date column as below ...
Columns("H:H").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
.NumberFormat = "dd/mm/yyyy"
End With
... and then saves the file as .xls.
If I run the macro from the standard toolbar (ie: Tools/Macro/Macros/) then everything works just great,
ie: CSV format "12/04/01" and "17/04/01" comes across as 12/04/2001 and 17/04/2001 respectively ... However:
If I run the same macro from a customised toolbar, the results are ... 04/12/2001 and 17/04/01 respectively.
A hell of a shame as customised toolbars are a handy facility for farming out files to users. Anyone know a way round this?
Posted by Dave Hawley on April 10, 2001 11:52 PM
Hi Aid
I just ran this macro from a button attached to the Excel menu bar:
Columns("H:H").NumberFormat = "dd/mm/yyyy"
...and It worked fine.
Ie; 12/04/01 and 17/04/01
became:
12/04/2001 and 17/04/2001
Perhaps your problem is elsewhere.
Dave
OzGrid Business Applications
Posted by Aid Burrow on April 11, 2001 2:58 PM
Re: Excel 97 customised toolbar, UK date format errors ... Thanks Dave, but ...
Dave ... thanks for the reply. It didn't solve my problem, but it did start me off on another tack.
What I didn't say 1st time round was that my macro starts by opening the CSV file (that contains dates in text format). My line of code is ..
Workbooks.Open Filename:="xxxxxx"
Where "xxxxxx" is the full path and filename.
I think that this is where my problem lays. After your reply, I created a noddy macro with the line of code that you had used ... against an already open CSV file it worked perfectly. I then inserted the line of code to open the file and re-ran the macro .... this is when it screws up.
I guess that the Workbooks.Open Filename statement in some way formats the dates before the subsequent code can get to it. I believe that a parameter can be declared for Format in the Open Filename call, but being a VBA novice I'm not sure how to go about this.
Any advice?
Ta .... Aid
