sum of excell range formated as text

[PWise]

i am connecting to excell via Automation
this is the basic code i am using

dim Xcell As Excel.Application
Set Xcell = CreateObject("Excel.Application")
Xcell.Workbooks.Open Path & "\" & ClaimNumber
amt = Xcell.WorksheetFunction.Sum(Xcell.Worksheets(1).Range(rst!Amount))

my problem is that if the range is formated as text it returns 0.

how can i get access to take it as a value

thank you

 

[SkipVought]

Hi,

Use a function like this...

Function SumTextValues(rng As Range) As Long
    Dim r As Range
    For Each r In rng
        If IsNumeric(r.Value) Then
            SumTextValues = SumTextValues + r.Value
        End If
    Next
End Function
...

your code
amt = SumTextValues(Xcell.Worksheets(1).Range("Amount"))

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[PHV]

A variation on Skip's function:
Function SumTextValues(rng As Range) As Long
Dim r As Range
For Each r In rng
SumTextValues = SumTextValues + Val(r.Value)
Next
End Function

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