Texttocolumns and Date Format using Macro - UK International setti

[Guest]

Would appreciate any help on this one. Using Excel 2003 / Windows XP SP2.

I import information from a database - this includes dates that are in
MM/DD/YYYY format. As I am in the UK, Excel's auto-features convert some of
the dates correctly, others incorrectly. I therefore use the
data/texttocolumns function to give correct dates - have done this many times.

I have just tried to record a macro using these steps (remembering to select
the MDY option for the date format). The result was perfect when recording.
However, when running the macro on fresh data, it failed - an example is a
data that when imported was
5/1/2007 (1 May 2007, US format)

and when manually changed using text to columns shows as
1/5/2007 (1 May 2007, UK format).

converting using the recorded macro gives the result
5/1/2007 (5 Jan 2007, UK format)

I suspect this may be caused in some way by the International Date settings
being ignored by the macro, but used in manual activity. However I have no
idea how to change the behaviour within a macro.

I could re-write the whole macro so that I parse each cell and assign the
values to the correct date component, but that would be much of a
sledge-hammer to crack a nut.

Thanks for reading this - hope you can help me.

 

[Tim Williams]

Can you just do the date formatting in the SQL before it arrives in Excel?

Tim

 

[Guest]

Unfortunately, no. The system I am extracting from is used globally, so the
extract/report would need to be different for each country format.

 

[Tim Williams]

How are you performing the import?

Tim

Unfortunately, no. The system I am extracting from is used globally, so
the
extract/report would need to be different for each country format.

 

[Guest]

Tim
Sorry for the delay - I have been on holiday for the past week.

The main database has a browser based report function. Once displayed in
the browser, the report is saved as a *.htm file. This is then pulled into
excel using a macro to clean up formatting issues (mainly merged cells).

I am pretty sure that the dates would not be an issue if the instance of
excel was running in US mode, it is just that my excel is in UK mode. Do you
know of any variables that can be set/reset within a macro to temporarily set
the programme in US mode?

Thanks

 

[Peter T]

Do you know of any variables that can be set/reset within a macro

to temporarily set the programme in US mode?

That would mean changing system date settings to US style, I think.
Maybe you could try something like this -

Sub Test2()
Dim s As String
Dim dt As Date
Dim vSplit

s = "03/06/07"
dt = CDate(s)
Debug.Print Format(dt, "dd mmm yyyy") '03 Jun 2007 in Int-date system

' parse the date, this is just one way which requires error handling (not
shown)
vSplit = Split(s, "/")

dt = DateSerial(vSplit(2), vSplit(0), vSplit(1))
Debug.Print Format(dt, "dd mmm yyyy") '06 Mar 2007

End Sub

 

[Guest]

Peter
Thanks for the suggestion - guess there is no simple fix to this
inconsistent behaviour of texttocolumns (user vs macro initiated).

As you indicate, this will involve a fair bit more coding for recalculate
each populated cell in the column and to cope with error conditions.