Convert strings to numbers 1

[snuv]

I have a spreadsheet that retrieves data from a database Whenever a user changes the settings on the spreadsheet, the data is refreshed.

The data has to be in string format on the database but I need to convert it to numeric on the spreadsheet

I could use a block of formulas to convert it
eg =IF(ISERR(VALUE(D8)),D8,VALUE(D8))
but it would be better if i could convert it before i dump it onto the sheet

does anyone know of a way to do this

the code I'm using is

        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=" & strDatabase & ";DefaultDir=" & strPath _
        ), Array( _
        " trend analysis;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTime" _
        ), Array("out=5;")), _
        Destination:=clTrgt)
         .CommandText = Array( _
        "SELECT " & strFieldList _
        , _
        "" & Chr(13) & "" & Chr(10) & "FROM " & strTable & " Where " & strFilterClause _
        )
        .Name = "SampleQuery"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

Thanks
Snuv


"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[PHV]

Have you tried to use the Val function in strFieldList ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snuv]

Thanks for your help

It worked

Cheers
Snuv

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary