+ Reply to Thread
Results 1 to 2 of 2

Excel 2003: Grabbing a dataset from a webservice and then sending to a webservice?

  1. #1
    gjn
    Guest

    Excel 2003: Grabbing a dataset from a webservice and then sending to a webservice?


    I am writing an application which uses Excel to fetch and update data
    from a database. I have been able to get the data from the WebService
    and get it into Excel...but I have not yet found a way to the data back
    to a WebService.

    The architecture I am using is:
    Excel 2003 workbook (with VBA)
    --includes SOAP 3.0 and MS Web Services Toolkit 2.01
    WebServices (via C#/.Net running on an IIS web server)
    MS SQL 2000 database (that the WebService attaches to)

    The webservice which I get the data from is (in C#):

    ---begin---
    [WebMethod]
    public DataSet GetHistoricalDoubleWeibullModelParameters(string
    fileLocation) {
    DataSet ds;
    ....
    return ds;
    }
    ---end---

    In Excel I am able to receive this code using:

    ---begin---
    Dim weibullWebService As clsws_WeibullWebService
    Dim historicalDataSet As MSXML2.IXMLDOMNodeList
    Set weibullWebService = New clsws_WeibullWebService
    Set historicalDataSet =
    weibullWebService.wsm_GetHistoricalDoubleWeibullModelParameters("parameter")
    ---end----

    However, I want to send this "historicalDataSet" back to a webservice
    in the form of a dataset.

    The webservice I want to send it to is:
    ---begin---
    [WebMethod]
    public double DesignWindSpeed_DoubleWeibullModel(DataSet weibullData,
    double targetReturnPeriod) {
    double x;
    ....
    return x;
    }
    ---end---

    I have tried using the code in Excel (which I know is wrong):
    ---begin---
    windSpeed =
    weibullWebService.wsm_DesignWindSpeed_DoubleWeibullModel(historicalDataSet,
    50)
    ---end---

    When I try to run macro that this is part of, the call breaks due to a
    casting error. What I can't figure out is how to convert a
    IXMLDOMNodeList object into a whatever Excel 20003 VBA will send as a
    dataset over SOAP for my webservice.

    What do I need to here to make this conversion?

    Any suggestions would be welcome gjn[at]rwdi.NOSPAM.com

    Graham


  2. #2
    gjn
    Guest

    Re: Excel 2003: Grabbing a dataset from a webservice and then sending to a webservice?

    I found this solution:

    Excel 2003 and the WebServices? Toolkit are not set up to pass datasets
    back and forth. You can do it but it takes seveal hundred lines of code
    to manually parse through the IXMLDOMNodeList? on the Excel end of
    things. If you try to send the IXMLDOMNodeList? back to the WebSevice?
    it is very very finicky.

    The WebService? tool kit for Microsoft Office will receive a dataset as
    a IXMLDOMNodeList? object. This object is pretty much useless, and
    cannot be used to pass a dataset back to an WebService?.

    The way to get around this is to send any datasets back and forth as
    XML strings.

    On the .Net WebService end of things:
    -------------------------------------

    TO SEND:
    using System.XML;
    [WebMethod]
    public string sendstuff() {
    DataSet ds = new DataSet("Name1");
    ...
    return ds.GetXML();
    }
    TO RECEIVE
    using System.XML;
    public int receivestuff(string xmlString){
    DataSet ds = new DataSet("Name2");
    StringReader stream = new StringReader(xmlString);
    ds.ReadXml(stream)
    ....
    return resultCode;
    }

    On the Excel 2003 side of things:
    ---------------------------------
    Preparation:
    -make sure the Microsoft Office Web Services tookit is installed
    -go to the Visual Basic Editor (Tools - Macro - Visual Basic Editor)
    and from its tools menu go to Web Services References.., in the dialog
    box, type in the exact URL for the WebService you are using (that you
    wrote as above) and click Search, then check it off and add it
    -use the webService to create a sample XML file for each type of XML
    string you want to send/receive, save this file so it has the same name
    of the DataSet you will use (e.g., Name1.xml and Name2.xml).
    -open Excel and install the XML Toolkit Add-In]
    -use the XML toolbar to open the "XML Schemas" pane
    -Add each of the of the xml files you created as a new map (do not use
    an .xsd file to ty to create the XmlMaps - there is a good chance that
    the XmlMap created this way will not be exportable)
    -map all the elements each of the xml files on its own sheet in your
    workbook

    -then use the following code:
    TO RECEIVE:
    Dim ws As clsws_YourWebService
    Dim ss As String
    Dim xmap As XmlMap
    ' attach to web service
    Set ws = New clsws_YourWebService
    ' get model parameters
    ss = ws.wsm_sendstuff()
    'put on spreadsheet
    Set xmap = ThisWorkbook.XmlMaps("Name1_Map")
    xmap.ImportXml (ss)
    TO SEND:
    Dim ws As clsws_YourWebService
    Dim ss As String
    Dim xmap As XmlMap
    Dim resultCode As Integer
    ' attach to web service
    Set ws = New clsws_YourWebService
    'get references we need from spreadsheet
    Set xmap = ThisWorkbook.XmlMaps("Name2")
    ' create XML string of data
    xmap.ExportXml data:=outstr
    ' call the webservice
    resultCode = ws.wsm_receivestuff(outstr)

    Anyway, this is what I found worked.

    Cheers,
    Graham


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1