Is that column formatted as a date before your macro runs?
Maybe change the numberformat for that column before converting it from text.

Sheet.Range(..).Numberformat = "0"


"Kc-Mass" wrote:

I have a large Access project that does a lot of export to Excel Wbs and
Shts. Much of the data arrives out of server level DBs.

Many columns initially arrive in Excel as though they were quoted text like
'3245876
They align left and cannot be used in math procedures.

I have a routine that sets those columns as a range and then applies a
"Sheet.Range.Value = Sheet.Range.Value. That works (converts data to
numerics) on all
but one column.

The column it does not work on has data that , like those mentioned above,
is numeric but comes in as quoted text.

The difference in this column is that it has 8 digits like 60113015. When
the procedure mentioned above converts this column, Excel apparently
believes that the data represents a date and converts it accordingly.
Because the resulting date is out of bounds, Excel
places ############# in each cell of the column.

If I type a 1 in one of the cells, it shows the result as 1/1/1900.

Is there a simple work around for this behavior in Excel.

Thx for any hints, solutions or sources.

Kevin