Excel VBA: output the result of a WinHTTPRequest in Excel?

Question

How do I output the result of a WinHTTPRequest in Excel?

For example, the following code queries the stock quote of Apple from a webpage but it doesn't output anything:

Sub GetQuotes()

     Dim XMLHTTP As Object, html As Object, pontod As Object

     On Error Resume Next
     Set oHtml = New HTMLDocument
     With CreateObject("WINHTTP.WinHTTPRequest.5.1")
         .Open "GET", "http://www.reuters.com/finance/stocks/overview?symbol=AAPL.O", False
         .send
         oHtml.body.innerHTML = .responseText
     End With

     'Price
     Set pontod = oHtml.getElementsByClassName("sectionQuote nasdaqChange")(0).getElementsByTagName("span")(1)
     MsgBox pontod.innerText

End Sub

While this runs perfectly for the name:

Sub GetQuotes2()

     Dim XMLHTTP As Object, html As Object, pontod As Object

     On Error Resume Next
     Set oHtml = New HTMLDocument
     With CreateObject("WINHTTP.WinHTTPRequest.5.1")
         .Open "GET", "http://www.reuters.com/finance/stocks/overview?symbol=AAPL.O", False
         .send
         oHtml.body.innerHTML = .responseText
     End With

     'Name
     Set pontod = oHtml.getElementById("sectionTitle").getElementsByTagName("h1")(0)
     MsgBox pontod.innerText

End Sub

I'd like to be able to fetch the whole page and look for specific HTML elements in it, but how do I manage to see the whole response from the query?

see [getElementsByClassName, HTMLSource's double quotation marks are gone](http://stackoverflow.com/questions/34302502/vba-getelementsbyclassname-htmlsources-double-quotation-marks-are-gone/34304544#34304544). — , Jan 07 '16 at 20:00
@Jeeped thanks, I got that .getElementById is working while .getElementsByClassName is not. but I'm still not there (I've read the post you indicate) — , Jan 07 '16 at 20:06
fwiw, when developing xmlHTTP requests, I use fiddler in a browser navigated to the same page that I am sending the request to. If you still need to see the whole page, a Ctrl+U in just about any browser will show the background HTML. Possible open a quick file object or ado.stream and write .responseText to a TXT or HTML. — , Jan 07 '16 at 20:10
In your first example, try replacing the declaration `html As Object` with `ohtml As HTMLDocument` and remove the "On Error Resume Next" line — barrowc, Jan 08 '16 at 01:10

Matteo NNZ · Accepted Answer · 2016-01-07T21:48:11.937

As Jeeped said above, the method getElementsByClassName doesn't work on an XML request.

However, by looking at the webpage you're trying to scrape, you can work-around the issue by using this line:

Set pontod = oHtml.getElementById("headerQuoteContainer").getElementsByTagName("span")(1)

instead of this one:

Set pontod = oHtml.getElementsByClassName("sectionQuote nasdaqChange")(0).getElementsByTagName("span")(1)

As you can observe from the HTML structure of the webpage:

... your price is not only the second span element of the first div with class names sectionQuote and nasdaqChange, but also the second span element of the unique object with id headerQuoteContainer.

Hence, scrape it from there will avoid you to use the invalid method getElementsByClassName (which is a valid HTML method, but not when the HTML is an XML response) for the classic getElementById().

Thanks, that's correct. It doesn't work with getElementsByClassName. It does work if instead of winhttprequest a browser an object browser is created to navigate to the desired page. — , Jan 07 '16 at 21:49

Excel VBA: output the result of a WinHTTPRequest in Excel?

1 Answers1