excel macro to change unformatted text data to correctly-typed data

Question

I'm populating an excel sheet from DTS, but the resulting output is formatted as text since DTS sees the cells as varchar(255). I'm planning on adding a macro that will format the values correctly as numeric or datetime. What VBA would I use for this?

eg the value in a cell shows as "2009-01-01 00:00:00". If I press F2 to edit the cell, then press ENTER, Excel realises it's a date and then formats it according to the cell formatting. I'd like to have some VBA to do the same thing.

The problem isn't that the cell-formatting is incorrect, the problem is that Excel is thinking of the data as text instead of datetime or numeric.

Answer 1

I asked a similar question some time back.Check it out.

Answer 2

Seems you can just assign the value of a cell or range to itself and that lets Excel determine the datatype. You can do this a row at a time, eg:

Worksheets("MySheet").Range("A:A").Value 
     = Worksheets("MySheet").Range("A:A").Value