VBA Problem Converting Numbers that are brought in as text
 

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

VBA Problem Converting Numbers that are brought in as text
 

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

VBA Problem Converting Numbers that are brought in as text
 

Thanks for the thought but I tried it and I get the same result as
previously

Thx
Kevin

"JMB" wrote in message
...
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

VBA Problem Converting Numbers that are brought in as text
 

On Sat, 30 Dec 2006 13:59:03 -0500, "Kc-Mass"
<connearney_AT_comcast_PERIOD_net wrote:

Thanks for the thought but I tried it and I get the same result as
previously

Thx
Kevin

Clearly, given the result when type a "1" in that column, something is
formatting the column to DATE.

Why not try change the format back to NUMBER (or General) AFTER you import the
values and run your SUB on them.
--ron

VBA Problem Converting Numbers that are brought in as text
 

When changing the format for a column or range from Date to General or
Number, you will need to make the format change and immediately save to file.
Then the format change should be effective.

"Kc-Mass" wrote:

Thanks for the thought but I tried it and I get the same result as
previously

Thx
Kevin

"JMB" wrote in message
...
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