none
Getting Excel to interpret the return string of a WEBSERVICE() function using a WCF REST service. RRS feed

  • Question

  • I am using the WEBSERVICE() function in Excel and a WCF REST service is called through this function.  

    The web service returns a string value of true or false when invoked.  E.g. <string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">True</string>

    This has been confirmed through debugging and it seems to be working fine when invoked through a client application or browser.

    However, the result shown in the cell in the Excel sheet is #VALUE!. This particular cell has a custom format (set using the Format Cells ... option) that displays "Yes" or "No". (The custom format is "Yes" ; "Yes" ; "No".)  Is this the correct way of interpreting the results of a Web Service method called in Excel? Does a #VALUE! result actually imply a formatting issue or some other interpretation error? Is there anything missing? Can somebody suggest ideas?

    Any help would be appreciated.

    Many thanks,

    Keith


    R K Howard

    Friday, February 13, 2015 6:42 PM

Answers

All replies

  • Hi Keith,

    If arguments result in a string that is not valid or that contains more than the allowable cell limit of 32767 characters, WEBSERVICE returns the #VALUE! error value.

    I suspect that your WCF data format is not compatible with Excel WEBSERVICE function.

    Monday, February 16, 2015 8:42 AM
  • Hi Keith,

    Have you used the formula FILTERXML(xml, xpath) function to extract single pieces of data from the XML string?

    If not, please follow the blog below, the author will guide you to get data from Webservice.

    http://blogs.office.com/2013/03/21/use-webservice-functions-to-automatically-update-excel-2013-spreadsheets-with-online-data/

    Best Regards

    Lan 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Luck.Vicky Thursday, February 26, 2015 9:36 AM
    • Marked as answer by L.HlModerator Friday, February 27, 2015 7:47 AM
    Monday, February 23, 2015 1:30 AM
    Moderator