Consulting

Results 1 to 6 of 6

Thread: Solved: Soap: need refresher

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    Solved: Soap: need refresher

    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 Stan

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by xld
    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.0

    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

  4. #4
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Figured it out. Marking solved.

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by xld
    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.

    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.

Posting Permissions

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