Get a Table from a Web Page with an XML Request

from www.dailydoesofexcel.com

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.