Change text data to correct datatype

Question

I'm populating an Excel sheet from DTS. The result is formatted as text since DTS sees the cells as varchar(255).

The cell-formatting is correct. The problem is that Excel is thinking of the data as text instead of datetime or numeric.

E.g. 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.

How can I format the values as numeric or datetime with VBA?

Answer 1

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