Archive for the ‘Automation’ Category.

Get a Table from a Web Page with an XML Request

December 15, 2011, 5:30 am by Dick Kusleika

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.

Category: Automation, External Data, VBA  |  1 Comment