Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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?
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
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