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.
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.