Error creating project using VS 2010 and Office Excel 2010

Question

Hi Guys,

Trying to consume a web service from a Excel 2010 workbook on a button click. Once I get results, I just need to show the results in the cells. To do this, I just followed the steps given in this Microsoft link:  http://msdn.microsoft.com/en-us/library/dd819156(v=office.12).aspx

When I run the app,  I am able to invoke the web service and get the results but it's failing with below exception while setting data into the cells.

The exception is:  {"Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))"}

 

Sending the sheet1.vb code for reference: 

Public Class Sheet1
    Private Sub Sheet1_Startup() Handles Me.Startup
        Me.Controls.AddNamedRange(Me.Range("A1", "A5"), "Data")
    End Sub
    Private Sub Sheet1_Shutdown() Handles Me.Shutdown
    End Sub
    Public Sub DisplayWebServiceResults(ByVal searchTerm As String)
        ' Clear the current display.
        Dim range As Tools.NamedRange = Me.Controls("Data")
        range.Clear()
        ' Call the Web service.
        Dim service As ClaimSearch.DuplicateClaimSearchAPIPortTypeClient = New ClaimSearch.DuplicateClaimSearchAPIPortTypeClient()
        Dim response() As ClaimSearch.findDuplicateClaimsResponseEntry = service.findDuplicateClaims(Nothing, Nothing, "234-23-4231", "2012-6-8", "MOORE", "0001165313")
        If Not (response Is Nothing) And (response.Length >= 0) Then
            ' Set the range.
            range.RefersToR1C1 = String.Format("=R1C1:R{0}C5", response.Length)
            For x = 0 To response.Length - 1
                Dim item As ClaimSearch.Claim = response(x).Claim
                ' Update data range, Excel uses 1 as the base for index.
                'Me.Range("A1").Value2 = item.claimant
                range.Cells(x + 1, 1).Value2 = item.claimant
                'range.Cells(x + 1, 2).Value2 = item.claimant
                'range.Cells(x + 1, 3).Value2 = item.claimant
                'range.Cells(x + 1, 4).Value2 = item.claimant
                'range.Cells(x + 1, 5).Value2 = item.claimant
            Next x
        End If
End Sub
End Class

I had tried what ever I see on setting the values to cells(Including the Microsoft guide) but still no chance to make the code work.  After all these,  I want to know whether code has any missing dependencies/problems.

 

Answer 1

Hi Chennakesava,

Thanks for posting in the MSDN Forum.

Does the webservice works fine on your side? Can that webservice works on other application without error?

It's based on my experience that isn't a problem of Excel PIA.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us