I'm dumping data into an Excel sheet via DTS from SQL Server. But I can't figure out how to get the numbers to come across as numbers. They come across as text and the formulas in the sheet are choking because they are expecting to sum numbers.

So I figured I'd just convert the data programmatically from text to numbers.

Changed the NumberFormat property and it doesn't help.
Tried messing with the PrefixCharacter property, but it's read only.

How can I get the values to convert from text to number? Excel offers the option as a SmartTag, but I can't find any method that will do it programmatically. Thanks.

Jerry

Nevermind... All I had to do was reassign the same value to the cell after setting the NumberFormat property.
"news.microsoft.com" wrote in message ...
I'm dumping data into an Excel sheet via DTS from SQL Server. But I can't figure out how to get the numbers to come across as numbers. They come across as text and the formulas in the sheet are choking because they are expecting to sum numbers.

So I figured I'd just convert the data programmatically from text to numbers.

Changed the NumberFormat property and it doesn't help.
Tried messing with the PrefixCharacter property, but it's read only.

How can I get the values to convert from text to number? Excel offers the option as a SmartTag, but I can't find any method that will do it programmatically. Thanks.

Jerry

It is pretty much a matter of stimulating Excel to reevaluate the value:

Sub convertBack()
Dim cell as Range
For each cell in selection
if not cell.Hasformula then
if isnumeric(trim(cell.Text)) then
cell.Numberformat = "general"
cell.formula = cdbl(trim(Replace(cell.value,chr(160),"")))
end if
end if
Next
end Sub



--
Regards,
Tom Ogilvy




"news.microsoft.com" wrote:

I'm dumping data into an Excel sheet via DTS from SQL Server. But I can't figure out how to get the numbers to come across as numbers. They come across as text and the formulas in the sheet are choking because they are expecting to sum numbers.

So I figured I'd just convert the data programmatically from text to numbers.

Changed the NumberFormat property and it doesn't help.
Tried messing with the PrefixCharacter property, but it's read only.

How can I get the values to convert from text to number? Excel offers the option as a SmartTag, but I can't find any method that will do it programmatically. Thanks