Get a Table from a Web Page with an XML Request

I’m tired of trying to remember the library names and the syntax for doing this. I’m putting here so when I search for XMLRequest, Web Request, HTTPRequest, I can find it.

Sub GetData()

Dim oHttp As MSXML2.XMLHTTP
Dim sHtml As String
Dim hDoc As HTMLDocument
Dim hTable As HTMLTable
Dim hRow As HTMLTableRow
Dim hCell As HTMLTableCell
Dim rStart As Range

Const sURL As String = "http://www.contextures.com/xlsampledata01.html"

Set oHttp = New MSXML2.XMLHTTP
Set hDoc = New HTMLDocument
Set rStart = Sheet1.Range("A1")

'Send the web request
oHttp.Open "GET", sURL
oHttp.send

'Give it enough time to process
Do
DoEvents
Loop Until oHttp.readyState = 4

'put the web page into an HTML Document
hDoc.body.innerHTML = oHttp.responseText

'Find the right table and write it to a sheet
For Each hTable In hDoc.all.tags("TABLE")
If hTable.Rows(0).Cells(0).innerText = "OrderDate" Then
For Each hRow In hTable.Rows
For Each hCell In hRow.Cells
rStart.Offset(hRow.RowIndex, hCell.cellIndex).Value = hCell.innerText
Next hCell
Next hRow
End If
Next hTable

End Sub

It sends a request to the sample data page over at Contextures, jams that into an HTML Doc, then writes the table out to a sheet.

One thought on “Get a Table from a Web Page with an XML Request

  1. So i just learned this a few weeks ago and was running into an issue that i was wondering if you had with doing xml pulls. i keep getting the following error:

    Run-time error ‘-2416697211 (800c0005)’:
    The system cannot locate the resource specified

    This happens when i’m running a massive query that runs through about 2000+ queries and i’m not sure what is causing it. it happens after about 3-500 queries and it’s happening on the .send command for the xmlhttp request. When i start over from the beginning of that particular loop, the code keeps running no problem.

    not sure how many searches you do at a time if you were running into the same thing or not.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax