none
VBA XML feed -- will not pull new data RRS feed

  • Question

  • I need to pull NOAA weather alerts into a workbook. Conveniently, they provide the feed in XML. Yesterday, I wrote the code below and everything seemed to be working fine. 

    However, when I ran the code this morning, nothing changed -- it retrieves yesterday's alerts. I can't figure out why. 

    The URL requested has been updated with today's information.

    Sub GetData() 'Tuesday, February 18, 2014 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '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 cell As Range Dim fn As WorksheetFunction Set fn = Application.WorksheetFunction Dim Eff, Exp As String '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'Delete current region and create titles '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ws.Range("A6").CurrentRegion.Clear '_snip_ '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'Get Weather Alerts '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Req.Open "GET", "http://alerts.weather.gov/cap/us.atom" Req.send Resp.LoadXML Req.responseText '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'Write alerts to worksheet ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    For Each Wthr In Resp.getElementsByTagName("entry") i = i + 1 ws.Cells(5 + i, 1).Value = Wthr.SelectNodes("cap:event")(0).Text Eff = Wthr.SelectNodes("cap:effective")(0).Text ws.Cells(5 + i, 2).Value = Left(Eff, fn.Search("T", Eff) - 1) ws.Cells(5 + i, 3).Value = Mid(Eff, fn.Search("T", Eff) + 1, 8) Exp = Wthr.SelectNodes("cap:expires")(0).Text ws.Cells(5 + i, 4).Value = Left(Exp, fn.Search("T", Exp) - 1) ws.Cells(5 + i, 5).Value = Mid(Exp, fn.Search("T", Exp) + 1, 8) ws.Cells(5 + i, 6).Value = Wthr.SelectNodes("cap:urgency")(0).Text ws.Cells(5 + i, 7).Value = Wthr.SelectNodes("cap:severity")(0).Text ws.Cells(5 + i, 8).Value = Wthr.SelectNodes("cap:certainty")(0).Text ws.Cells(5 + i, 9).Value = Wthr.SelectNodes("cap:geocode")(0).Text ws.Cells(5 + i, 10).Value = Wthr.SelectNodes("summary")(0).Text ws.Cells(5 + i, 11).Value = Wthr.SelectNodes("id")(0).Text Next Wthr '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'Reformat Table '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '_snip_ End Sub



    • Edited by M1138 Wednesday, February 19, 2014 2:03 PM formatting
    • Moved by Marvin_Guo Thursday, February 20, 2014 7:04 AM better forum
    Wednesday, February 19, 2014 2:02 PM

Answers

  • Solution: 

    Change the following:

    Dim Req As New XMLHTTP

    To

    Dim Req As New ServerXMLHTTP
     

    And change:

    Req.Open "GET", "http://alerts.weather.gov/cap/us.atom"

    To 

    Req.Open "GET", "http://alerts.weather.gov/cap/us.atom", False


    • Marked as answer by M1138 Monday, February 24, 2014 3:30 PM
    Monday, February 24, 2014 3:30 PM

All replies

  • Hi ,

    I think the key point of this case is how to use the XMLhttp in MSXML assembly,I suggest you to set break point to check whether Resp object return correct XML data.

    BTW, since this forum mainly discusses object model of Excel. I will help you move it to VBA forum (http://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba) for more efficient responses, where you can contact VBA experts.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 20, 2014 7:03 AM
  • Break point set.

    I guess it depends on what you mean by correct. It returns the same data set every time, so I suspect it's caching the xml feed. It was right the first time the code ran, and has been wrong in subsequent runs -- wrong in the sense that it is returning the initial feed and not the changed one currently loaded to the site. 

    Is there a method for clearing the cache? My search for such a method has been fruitless. 

     
    Thursday, February 20, 2014 3:25 PM
  • More detail:

    req.readyState = 4

    req.status = 0

    I'm not sure how I'm getting a ready state of 4 (meaning my request has been received, processed, and is ready) and a status of 0. 

    Thursday, February 20, 2014 9:19 PM
  • Solution: 

    Change the following:

    Dim Req As New XMLHTTP

    To

    Dim Req As New ServerXMLHTTP
     

    And change:

    Req.Open "GET", "http://alerts.weather.gov/cap/us.atom"

    To 

    Req.Open "GET", "http://alerts.weather.gov/cap/us.atom", False


    • Marked as answer by M1138 Monday, February 24, 2014 3:30 PM
    Monday, February 24, 2014 3:30 PM