making a web service call from excel 2000 in vba 1

[Expressline99]

Hello, I need to send out a webservice call from VBA in excel 2000...that looks like this...>>>>

http://www.zillow.com/webservice/Ge...ess=2114+Bigelow+Ave&citystatezip=Seattle,+WA

>>>>>>>>
This will return a page of xml that I will need to parse...once I get the page in to excel I can do everything else. I'm just at a loss as to how to do this via VBA.

Thanks,

Paul

 

[CautionMP]

Expressline99,
It's probably more than you asked for (I'm working on an XML project and I need the practice) but I hope it helps.

Sub Test()
  Const strcURL As String = "[URL unfurl="true"]http://www.zillow.com/webservice/GetDeepSearchResults.htm"[/URL] & _
"?zws-id=<ZWSID>&address=2114+Bigelow+Ave&citystatezip=Seattle%2C+WA"
  Dim objWinHTTPReq  'WinHttpRequest
  Dim objXMLDomDoc   'DOMDocument
  Dim objXMLNodeList 'IXMLDOMNodeList
  Dim objXMLNode     'IXMLDOMNode
  Dim objXMLElement  'IXMLDOMElement
  
  'Initalize some stuff
  Set objWinHTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
  Set objXMLDomDoc = CreateObject("Msxml2.DOMDocument")
  
  'Make the web call
  objWinHTTPReq.Open "GET", strcURL
  objWinHTTPReq.Send
  
  'Load the XML returned
  objXMLDomDoc.loadXML objWinHTTPReq.ResponseText
  
  'Grab the 'request'
  Set objXMLNodeList = objXMLDomDoc.getElementsByTagName("request")
  'cycle though it's nodes
  For Each objXMLNode In objXMLNodeList
    'spit out the elements to the immediate window
    For Each objXMLElement In objXMLNode.childNodes
      Debug.Print objXMLElement.nodeName, objXMLElement.nodeValue
    Next objXMLElement
  Next objXMLNode
  
  'Clean up
  Set objXMLElement = Nothing
  Set objXMLNode = Nothing
  Set objXMLNodeList = Nothing
  Set objXMLDomDoc = Nothing
  Set objWinHTTPReq = Nothing
End Sub

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Expressline99]

Wow looks great. I've inserted my ZWSID into the url line...however I don't seem to get any output from it.
When I paste the url with that ID in it I get a raw url page. Which is what we are going for. So I"m confused. Shouldn't the data be printed to the current worksheet?
Probably I'm so confused I'm missing the point lol.
It didn't error out or anything. To run it I simply put a
cmd button on the page and called the sub.

I didn't post my ID since there is a limit of requests per day on that.

If you needed an ID its free from them. I might have to beg you to try it out that way. Either way I'm happy I can see some light at the end of the tunnel. So thank you for that.

If there is any more data I can supply please by all means let me know. Here is their page with their description of it.

http://www.zillow.com/howto/api/GetDeepSearchResults.htm

Thanks,
Paul

 

[CautionMP]

Shouldn't the data be printed to the current worksheet?

Not as written. The [tt]Debug.Print[/tt] means it should be dumping the output to the immediate window of the Microsoft Visual Basic IDE.

From the window with the code in it:

[tab]View => Immediate Window
[tab]or Ctrl + G

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Expressline99]

Ah well that gets me somewhere I'm getting the following:

address Null
citystatezip Null

Paul

 

[Expressline99]

the following is the sample output file they give on the site:

>>>>>>>>>>>>>>>>>>>

<?xml version="1.0" encoding="utf-8" ?>
<SearchResults:searchresults xmlns:SearchResults="

http://www.

zillow.com/vstatic/3/static/xsd/SearchResults.xsd">
<request>
<address>2114 Bigelow Ave</address>
<citystatezip>Seattle, WA</citystatezip>
</request>
<message>
<text>Request successfully processed</text>
<code>0</code>
</message>
<response>
<results>
<result>
<zpid>48749425</zpid>
<links>
<homedetails>

http://www.zillow.com/HomeDetails.h...&zprop=48749425&partner=<ZWSID></homedetails>

<graphsanddata>

http://www.zillow.com/Charts.htm?ch...+476WWlb99DE=&partner=<ZWSID></graphsanddata>

<mapthishome>

http://www.zillow.com/search/Search.htm?mode=search&zpid=48749425&partner=<ZWSID></mapthishome>

<myestimator>

http://www.zillow.com/myestimator/Intro.htm?zprop=48749425&partner=<ZWSID></myestimator>

<myzestimator deprecated="true">

http://www.zillow.com/myestimator/Intro.htm?zprop=48749425&partner=<ZWSID></myzestimator>

</links>
<address>
<street>2114 Bigelow Ave N</street>
<zipcode>98109</zipcode>
<city>SEATTLE</city>
<state>WA</state>
<latitude>47.637934</latitude>
<longitude>-122.347936</longitude>
</address>
<FIPScounty>033</FIPScounty>
<useCode>SingleFamily</useCode>
<yearBuilt>1924</yearBuilt>
<lotSizeSqFt>4680</lotSizeSqFt>
<finishedSqFt>3290</finishedSqFt>
<bathrooms>2.75</bathrooms>
<bedrooms>4</bedrooms>
<lastSoldDate>06/18/2002</lastSoldDate>
<lastSoldPrice currency="USD">770000</lastSoldPrice>
<valuation>
<amount currency="USD">1091061</amount>
<last-updated>05/26/2006</last-updated>
<oneWeekChange currency="USD">61096</oneWeekChange>
<valuationRange>
<low currency="USD">938312</low>
<high currency="USD">1200167</high>
</valuationRange>
<percentile />
</valuation>
</result>
</results>
</response>
</SearchResults:searchresults>
<!--
H:33 T:19ms S:1462 R:Tue Sep 12 19:21:14 UTC 2006 B:2.0.11690_060911_1




>>>>>>>>>>
Thats what I will have to parse.

 

[Expressline99]

Well I'm getting it as I go. I figured out I needed to change.

This line:

Debug.Print objXMLElement.nodeName, objXMLElement.nodeValue


to this:

Debug.Print objXMLElement.nodeName, objXMLElement.Text

Not sure why nodevalue would only return null. However, ".Text" seem to return the value.

Changing the Element tag name helped me as well. Probably all the things I should have let you know to begin with. I certainly think I'll have this licked in no time.

Thanks,
Paul

 

[Expressline99]

Well now I'm confused. They supply a XML schema file which points to several others. Am I supposed to use this to gather all the elements/nodes/types? My goal here is only to grab a few items from the result XML file. Which I know the Tagnames. So I'm not sure if its needed. If it is....oh goody.



Paul

 

[Expressline99]

For those interested here is a link to get started on the object model of XML. I'm still working my way through it. Lots of information for someone who hasn't worked with it before. This begins the talk about XML and the Microsoft® XML Parser (Msxml.dll).

http://msdn.microsoft.com/en-us/library/aa468547.aspx

Paul

 

[Expressline99]

Since I'm learning as I go I'll answer my own question about the schema file. That file is used by the XLM parser to validate the XML file and is pointed to in the first couple of lines in the XML file. The schema file ending in .xsd