Convert numbers stored as text to numbers errors after loading data in

  • Thread starter Thread starter jobs
  • Start date Start date
J

jobs

I have an excel spreedsheet that already has cells formated.

I'm trying to write data to excel from sql server (i've tried using
the jet 4.0 oledb driver and ssis's destination excel) however when I
write rows, it insist on loading data as text, so I get the excel cell
formating error tag on every cell. Any way around this?.

from another tool i've been able to work around this by writing this
to the top of the file, but that was for the web based version of
excel used by asp.net.

<style>.text { mso-number-format:\@; } </style>

the exact error is a green tag on every cell, help on it reads:

Convert numbers stored as text to numbers
 
Assuming you want VBA to convert them, something like:
Sheets("Sheet1").Range("A2:F100").NumberFormat = "0.0"
will do so. Change sheet name, Range and format to suit.

Hth,
Merjet
 
To fix the cells that have already been entered:

Sub numerify()
Dim r As Range, rt As Range
Count = 0
Set rt = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 2)
For Each r In rt
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
Next
MsgBox (Count & " cells changed")
End Sub
 
Back
Top