API using Microsoft excel

  • 1
  • Question
  • Updated 4 years ago
Tried the example to get data using API in to Microsoft Excel, getting error "#value" (used formula webservice(url.xml))., Using same url I am able to see the data in the browser. Could you help me with this problem.
Photo of khans

khans

  • 3 Posts
  • 0 Reply Likes

Posted 4 years ago

  • 1
Photo of tgalima

tgalima

  • 3 Posts
  • 2 Reply Likes
You need to add reference to Microsoft XML, v3.0

Sub Wunderground()
Dim i As Integer, yy As Integer, mm As Integer, dd As Integer
Dim req As New XMLHTTP
Dim resp As New DOMDocument
Dim Weather As IXMLDOMNode
Dim r As Range

With Sheets("Main")
.Cells.Delete
Set r = .Range("A1")
End With

req.Open "GET", "http://api.wunderground.com/api//yest...", False
req.send
resp.LoadXML req.responseText

For Each Weather In resp.getElementsByTagName("observation")
i = i + 1
With r
yy = Weather.SelectSingleNode("date/year").Text
mm = Weather.SelectSingleNode("date/mon").Text
dd = Weather.SelectSingleNode("date/mday").Text
.Value = DateSerial(yy, mm, dd)
With .Offset(0, 1)
.Value = Weather.SelectSingleNode("date/hour").Text
With .Offset(0, 1)
.Value = Weather.SelectSingleNode("conds").Text
With .Offset(0, 1)
.Value = Weather.SelectSingleNode("tempi").Text
End With
End With
End With
End With
Set r = r.Offset(1)
Next

Set r = Nothing
Set req = Nothing
Set resp = Nothing
Set Weather = Nothing
End Sub
Photo of khans

khans

  • 3 Posts
  • 0 Reply Likes
Thanks.

Do I have to activate any references as I am getting "compile error : user defined type not defined" for the objects XMLHTTP, DOMDocument, IXMLDOMNode when I run the code.
Photo of tgalima

tgalima

  • 3 Posts
  • 2 Reply Likes
As noted above, in 'Tools/References' add reference to Microsoft XML, v3.0
Photo of khans

khans

  • 3 Posts
  • 0 Reply Likes
Sorry I missed your first comment.

Yes it is working fine.
Thank you and appreciate for your support