Search code examples
excelvbadts

Change text data to correct datatype

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?

Solution

  • 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