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

[gjn]

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

 

[gjn]

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