How do I pull XML data from a website into EXCEL

Hello,
Can someone point me in the right direction to how I can pull data from a website using vba code? I can generate the XML data, (attached) but do not know how to extract it. I've never extracted data from a website so I have no idea where to begin (but I do have moderate knowledge in vba).

The XML data is weather information and I'm mainly interested in the <dailysummary> section, <maxtempi> and <mintempi>

The XML is generated through an API call to Weather Underground:
http://api.wunderground.com/api/<key>/history_20110105/q/pws:KCOARVAD22.xml
<key> is a personal key assigned by Wunderground


Thanks KCOARVAD22.xml
bikeskiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

experts1Commented:
Example Subroutine below will get data from your target URL
and display it in MSGBOX.




Private Sub GetData()
    Dim objXML As XMLHTTP
    Dim strHTML As String
    Dim strUrl As String
    
    strUrl = "http://api.wunderground.com/api/<key>/history_20110105/q/pws:KCOARVAD22.xml"
    
    Set objXML = CreateObject("MicroSoft.XMLHTTP")
    objXML.Open "GET", strUrl, False
    objXML.send
    strHTML = objXML.responseText
    MsgBox strHTML
    
    Set objXML = Nothing
End Sub

Open in new window

0
bikeskiAuthor Commented:
The subroutine does pull the XML data into the message box, although the displayed data is truncated.

I guess I'm not as good at VBA as I thought. How do I parse out the <maxtempi>  and <mintempi> values?
0
experts1Commented:
Please post the actual text of the data which was returned,
and we will attempt to parse <maxtempi> and associated values.


0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

bikeskiAuthor Commented:
The actual text is in the XML document attached to the original question. Do you prefer it in another format?
0
experts1Commented:
Ok, sorry about delay been busy!

You said that the data returned was truncated,so I
wanted to see if it was actually different from the data in
your original file.

However, try modified sub below:
 
Private Sub GetData()
    Dim objXML As XMLHTTP
    Dim strHTML As String
    Dim strUrl As String
    
    strUrl = "http://api.wunderground.com/api/<key>/history_20110105/q/pws:KCOARVAD22.xml"
    
    Set objXML = CreateObject("MicroSoft.XMLHTTP")
    objXML.Open "GET", strUrl, False
    objXML.send
    strHTML = objXML.responseText
    'MsgBox strHTML
    
    Set objXML = Nothing

    Dim strTemp As String
    Dim strLen As Integer
    Dim tempiStart As Integer
    Dim tempiEnd As Integer
    Dim strTempi As String

    strTemp = strHTML
    strLen = Len(strTemp)
    If strLen > 21 Then
      tempiStart = InStr(strTemp, "<maxtempi>")
      tempiEnd = InStr(strTemp, "</maxtempi>")
      strTempi = Trim(Mid(strTemp, tempiStart + 10, tempiEnd - (tempiStart + 10)))
    End If
    MsgBox strTempi
End Sub

Open in new window

0
bikeskiAuthor Commented:
I tired the new code and got an run time error '5':
Invalid procedure call or argument on

strTempi = Trim(Mid(strTemp, tempiStart + 10, tempiEnd - (tempiStart + 10)))

The value for
strLen was 260, thus it never found <maxtempi> or </maxtempi> and strTemp

Below is a screen capture of message box, total lines and character count of the XML file,
2103 lines and 48,276  charaters
 picture of MsgBox and XML totals lines and charaters
I know that Strings can be up to 2 billion characters in length, so that should be a limiter.
 
0
bikeskiAuthor Commented:
noticed I made a mistake in my response above...comment should read:

I know that Strings can be up to 2 billion characters in length, so that should NOT be a limiter.

Can comments be edited after they are posted?
0
bikeskiAuthor Commented:
FYI, I tested the following code on data that's at the top of the XML file and it works fine.

      tempiStart = InStr(strTemp, "<version>")
      tempiEnd = InStr(strTemp, "</version>")
0
experts1Commented:
Please remember that ur original question was already answered
but we are just tweaking some issues here.
However, I suspect "<maxtempi>XX</maxtempi>" might not be in the data string.

So try mod below to avoid error:
note: if "<maxtempi>" not in string then msgbox will be empty
 

Private Sub GetData()
    Dim objXML As XMLHTTP
    Dim strHTML As String
    Dim strUrl As String
    
    strUrl = "http://api.wunderground.com/api/<key>/history_20110105/q/pws:KCOARVAD22.xml"
    
    Set objXML = CreateObject("MicroSoft.XMLHTTP")
    objXML.Open "GET", strUrl, False
    objXML.send
    strHTML = objXML.responseText
    'MsgBox strHTML
    
    Set objXML = Nothing

    Dim strTemp As String
    Dim strLen As Integer
    Dim tempiStart As Integer
    Dim tempiEnd As Integer
    Dim strTempi As String

    strTemp = strHTML
    strLen = Len(strTemp)
    'Check if <maxtempi> is in string
    If strLen > 21 And InStr(strTemp, "<maxtempi>") Then 
      tempiStart = InStr(strTemp, "<maxtempi>")
      tempiEnd = InStr(strTemp, "</maxtempi>")
      strTempi = Trim(Mid(strTemp, tempiStart + 10, tempiEnd - (tempiStart + 10)))
    End If
    MsgBox strTempi
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bikeskiAuthor Commented:
Oops, I found my error. I forgot to replace the <key> value with my real key in my url line. The original question is answered.

However, now I'm getting a runtime error 6;overflow on the Instr() function. I think the +48,000 length string is causing some problems. I  can post this as new question.

Thanks
0
experts1Commented:
Now that you revealed the string length to be actually +48,000 characters.

That might explain the quirky truncation you mentioned earlier.

Try changing the declaration of both strHTML and strTemp to Variant
data type as in updated mod below.

Note: a Variant has much bigger data capacity than a String


Private Sub GetData()
    Dim objXML As XMLHTTP
    Dim strHTML As Variant
    Dim strUrl As String
    
    strUrl = "http://api.wunderground.com/api/<key>/history_20110105/q/pws:KCOARVAD22.xml"
    
    Set objXML = CreateObject("MicroSoft.XMLHTTP")
    objXML.Open "GET", strUrl, False
    objXML.send
    strHTML = objXML.responseText
    'MsgBox strHTML
    
    Set objXML = Nothing

    Dim strTemp As Variant
    Dim strLen As Integer
    Dim tempiStart As Integer
    Dim tempiEnd As Integer
    Dim strTempi As String

    strTemp = strHTML
    strLen = Len(strTemp)
    'Check if <maxtempi> is in string
    If strLen > 21 And InStr(strTemp, "<maxtempi>") Then 
      tempiStart = InStr(strTemp, "<maxtempi>")
      tempiEnd = InStr(strTemp, "</maxtempi>")
      strTempi = Trim(Mid(strTemp, tempiStart + 10, tempiEnd - (tempiStart + 10)))
    End If
    MsgBox strTempi
End Sub

Open in new window

0
bikeskiAuthor Commented:
Thanks, changing to the Variant type worked. I also needed to change the tempiStart and tempiEnd variables to Long.

I made a few changes to the code to allow for variable data points.
Private Sub GetData()
    Dim objXML As XMLHTTP
    Dim strHTML As Variant
    Dim strUrl As String
    
    strUrl = "http://api.wunderground.com/api/<key>/history_20110105/q/pws:KCOARVAD22.xml"
    
    Set objXML = CreateObject("MicroSoft.XMLHTTP")
    objXML.Open "GET", strUrl, False
    objXML.send
    strHTML = objXML.responseText
    
    Set objXML = Nothing

    Dim strTemp As Variant
    Dim strLen As Integer
    Dim tempiStart As Long
    Dim tempiEnd As Long
    Dim strTempi As Variant
    Dim strDataPoint As String
    Dim strDataPointStart, StrDataPointEnd As String
    Dim strLenDataPointStart, strLenDataPointEnd As String

    strTemp = strHTML
    strDataPoint = "maxtempi"
    strDataPointStart = "<" + strDataPoint + ">"
    StrDataPointEnd = "</" + strDataPoint + ">"
    strLenDataPointStart = Len(strDataPointStart)
    strLenDataPointEnd = Len(StrDataPointEnd)
    
    'Check if <maxtempi> is in string
    If InStr(1, strTemp, strDataPointStart) Then
      tempiStart = InStr(1, strTemp, strDataPointStart)
      tempiEnd = InStr(tempiStart, strTemp, StrDataPointEnd)
      strTempi = Trim(Mid(strTemp, tempiStart + strLenDataPointStart, tempiEnd - (tempiStart + strLenDataPointEnd - 1)))
    End If
    MsgBox strTempi

End Sub

Open in new window

0
experts1Commented:
Cheers!

Great work bikeski!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.