Numbers stored as text

[Guest]

I am having a problem with numbers being stored as text in Excel. I am
exporting the data from a SQL Server dB using a DTS package. I have tried
converting the fields before export in DTS and this does seem to work
temporarily, but the Excel file will eventually convert to all text.

I have read the help regarding converting these fields from "numbers stored
as text" to numbers, and have forwarded that information on to the end users.
However, this can be only a temporary solution, as the end users do not want
to do this every day.

Thanks in advance for any help!

 

[Guest]

If the data is contiguous you can modify the following VBA code to convert
the string values to numeric values. This way your staff can just run a
macro following the retrieval of the data, which might make it easier for
them.

Sub TextToVal()

Dim wb As Workbook
Dim ws As Worksheet
Dim strVal As String
Dim lngVal As Long
Dim lCounter As Long
Dim r As Range

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")
ws.Activate
Range("A1").Select

Selection.CurrentRegion.Select
Selection.Name = "MyData"
Set r = Range("MyData")

For lCounter = 1 To r.Cells.Count
strVal = CStr(r.Cells(lCounter).Value)
lngVal = CLng(strVal)
r.Cells(lCounter).Value = lngVal
Next lCounter

Set wb = Nothing
Set ws = Nothing
Set r = Nothing

End Sub