Printable View
I feel pretty lame doing this, but I have attached a workbook with a Soap class generated with the Webservice Toolkit. I will be asked to perform some acute webservice requests so I decieded to practice with the free webservice
http://ws.cdyne.com/WeatherWS/Weather.asmx?wsdl
which gives weather by zipcode. So, the class gets generated, but what if I wanted the weather for zipcode 27612. How do I call the class methods to get results.
Really, really appreciate a response: pray2: : pray2: Stan
Stan,
I admit to knowing nothing about SOAP, but I am ready to look. What do I do with this stuff, the Excel file is just a bunch of classes, the link goes to an XML file. Do I need anything installed?
You will need SOAP30 installed and MSXML - if you open the workbook the references should be checked [see image]. I have MSXML 6.0 installed, the fallback is 3.0Quote:
Originally Posted by xld
1. just navigate to the ?wsdl udl in my first post. It should bring up xml data, and reference a method GetCityForecastByZIP() which takes a parameter of a zipcode.
2. Soap is merely an interface to obtain (for this particular webservice) weather information for the passed zip. That information will be passed back (the Webservice Toolkit creates a class for the structure that is returned) and can be parsed by the XML DomDocument. So, If I requested for "27603" I would get a response like:
[VBA]
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetCityWeatherByZIPResponse xmlns="http://ws.cdyne.com/WeatherWS/">
<GetCityWeatherByZIPResult>
<Success>true</Success>
<ResponseText>City Found</ResponseText>
<State>NC</State>
<City>Raleigh</City>
<WeatherStationCity>Raleigh</WeatherStationCity>
<WeatherID>3</WeatherID>
<Description>Mostly Cloudy</Description>
<Temperature>68</Temperature>
<RelativeHumidity>81</RelativeHumidity>
<Wind>SW12</Wind>
<Pressure>29.97S</Pressure>
<Visibility /><WindChill>0</WindChill>
<Remarks />
</GetCityWeatherByZIPResult>
</GetCityWeatherByZIPResponse>
</soap:Body></soap:Envelope>
[/VBA]
3. I got this information by using an alternate method, whereby you submit a 'soap envelope' via
objXML = Createobject("WinHttp.WinHttpRequest.5.1")
or
objXML = ObjectCreate("Microsoft.XMLHTTP")
and I would be happy to post the alternative. However, I will be engaged with dozens of WSDL urls and perhaps a thousand requests per day, and the benefit of having the class code auto-generated is worth investigating.
Stan
Figured it out. Marking solved.
Can you tell us Stan.
I tried to download the SOAP 3.0 Toolkit and it said it was deprectaed, and been absorbed within the Net framework?
you just need to check for MSOSOAP.SoapClient30 with regedit, it should be installed, or will be if you download the webservices toolkit.Quote:
Originally Posted by xld
The point is that using webservices in Excel generates (as you saw) an incredibly verbose and arcane assortment of class code. What it boils down to is to instantiate SOAP then call the specific function for the webservice - the result is returned as a NodeList which can then be parsed w/out having to instantiate a DOM Parser Object. This is what threw me off, as I haven't worked with SOAP for 3 years. I was expecting the return to be of type XML (string).
This means that the generated class code can be boiled down to 5-6 lines of VBA code and get the same results.... Basically (1)generate the class code (2) take what little you need from it to write a sub (3) discard the class code
If you, or anyone else is seriously interested, I can post a workbook with a module. Our company is going 100% webservices with respect to data transfer and this appears a trend to reduce Internet business traffic. I found out yesterday that our plan is to use Ruby on a Linux Server, but I argued a Windows alternative is worthwhile.
You can Google 'free webservices' and find dozens that offer stock quotes, weather, geographic information... use the webservice toolkit to create class code then retrofit to get the data more economically.
Note: there is an alternative to SOAP - called REST, so this is really interesting.:beerchug: