I modified this existing code to parse out some XML data from the national weather service feed. It runs on my home computer (excel 2007) without issue. It does not run on my work pc/network with excel 2013.
The following error is thrown.
Run-time error '-2147012894 (80072ee2)':
Automation error
Any insight? network issue or 2007 to 2013 compatibility issue? I can resolve the feed within my browser without issue.
Option Explicit
Sub GetData()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Variables
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ws As Worksheet
Set ws = ActiveSheet
Dim Req As New ServerXMLHTTP
Dim Resp As New DOMDocument
Dim i As Integer
Dim Wthr As IXMLDOMNode
Dim geo As IXMLDOMNode
Dim cell As Range
Dim fn As WorksheetFunction
Dim y As Integer
Dim x As Integer
Set fn = Application.WorksheetFunction
Dim Eff, Exp, et, severity As String
Dim splitstring As Variant
ws.Range("A6").CurrentRegion.Clear
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Get Weather Alerts
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Req.Open "GET", "https://alerts.weather.gov/cap/us.atom", False
Req.send
Resp.LoadXML Req.responseText
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Write alerts to worksheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
y = 1
For Each Wthr In Resp.getElementsByTagName("entry")
i = i + 1
severity = Wthr.SelectNodes("cap:severity")(0).Text
et = Wthr.SelectNodes("cap:event")(0).Text
For Each geo In Wthr.SelectNodes("cap:geocode")
splitstring = Split(geo.SelectNodes("value")(0).Text, " ")
For x = LBound(splitstring) To UBound(splitstring)
y = y + 1
ws.Cells(y, 1).Value = splitstring(x)
ws.Cells(y, 2).Value = severity
ws.Cells(y, 3).Value = et
Next x
Next
Next Wthr
End Sub
It appears the issue was ServerXMLHTTP. ServerXMLHTTP does not auto-discover proxy settings and my company uses a proxy script (I should have included this information).
The code runs if I use XMLHTTP. I believe the code used ServerXMLHTTP because of cache issues so I'm not sure if I will run into other issues.
Revised Code
Option Explicit
Sub GetData()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Variables
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ws As Worksheet
Set ws = ActiveSheet
Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Dim i As Integer
Dim Wthr As IXMLDOMNode
Dim geo As IXMLDOMNode
Dim cell As Range
Dim fn As WorksheetFunction
Dim y As Integer
Dim x As Integer
Set fn = Application.WorksheetFunction
Dim Eff, Exp, et, severity As String
Dim splitstring As Variant
ws.Range("A6").CurrentRegion.Clear
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Get Weather Alerts
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Req.Open "GET", "https://alerts.weather.gov/cap/us.atom", False
Req.send
Resp.LoadXML Req.responseText
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Write alerts to worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
y = 1
For Each Wthr In Resp.getElementsByTagName("entry")
i = i + 1
severity = Wthr.SelectNodes("cap:severity")(0).Text
et = Wthr.SelectNodes("cap:event")(0).Text
For Each geo In Wthr.SelectNodes("cap:geocode")
splitstring = Split(geo.SelectNodes("value")(0).Text, " ")
For x = LBound(splitstring) To UBound(splitstring)
y = y + 1
ws.Cells(y, 1).Value = splitstring(x)
ws.Cells(y, 2).Value = severity
ws.Cells(y, 3).Value = et
Next x
Next
Next Wthr
End Sub