XMLHTTP navigation and obtaining table.

[Trebor8484]

Hi all,

The code below works fine in order to copy a table from a website into Excel.

Sub GetData()


    Dim XMLPage As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim URL As String
    
    URL = "https://www.x-rates.com/table/?from=USD&amount=10"
    
    XMLPage.Open "GET", URL, False
    XMLPage.send
    
    HTMLDoc.body.innerHTML = XMLPage.responseText
    
    ProcessHTMLPage HTMLDoc
    
End Sub


Sub ProcessHTMLPage(HTMLPage As MSHTML.HTMLDocument)


    Dim HTMLTable As MSHTML.IHTMLElement
    Dim HTMLTables As MSHTML.IHTMLElementCollection
    Dim HTMLRow As MSHTML.IHTMLElement
    Dim HTMLCell As MSHTML.IHTMLElement
    Dim RowNum As Long, ColNum As Integer


    Set HTMLTables = HTMLPage.getElementsByTagName("table")


    For Each HTMLTable In HTMLTables
        If HTMLTable.Rows(0).Cells(0).innerText = "US Dollar" Then
            Worksheets.Add
            Range("A1").Value = HTMLTable.className
            Range("B1").Value = Now


            RowNum = 2
            For Each HTMLRow In HTMLTable.getElementsByTagName("tr")
                'Debug.Print vbTab & HTMLRow.innerText


                ColNum = 1
                For Each HTMLCell In HTMLRow.Children
                    Cells(RowNum, ColNum) = HTMLCell.innerText
                    ColNum = ColNum + 1
                Next HTMLCell


                RowNum = RowNum + 1
            Next HTMLRow
        End If
    Next HTMLTable
    
End Sub

My goal now is to use this approach rather than using an Internet Explorer object to go to a different website, click some buttons and copy the resulting table.

The steps will be:

1. Navigate to https://en.wikipedia.org/wiki/Main_Page as an example
2. Enter a search string in the search field, i.e. "Help:Table". ID for the search field is "searchInput" and ID for search button is "searchButton". I'm aware I could skip these first two steps and navigate directly to https://en.wikipedia.org/wiki/Help:Table but I need to test this approach for something I'm doing at work.
3. Final step is copy a table into Excel. There is a table about half way down the page called " Countries by percent of Avaaz members per popul" which could be used as an example.

If this is only possible using an internet explorer object then that would be fine as I could hide the session. The only reason I was keen to avoid it is because when I usually do this, it involved filling it fields and downloading a file and I then have to use sendkeys to save the file when a prompt appears in Internet Explorer. Copying the table directly from the web page seems like a more efficient way of doing it.

Thanks

 

[strooman]

My goal now is to use this approach rather than using an Internet Explorer object to go to a different website, click some buttons and copy the resulting table.

And what website would that be and what table data you want to fetch from that website? Try to be precise because for me it's not clear if you try to fetch data from the wikipedia website or from the x-rates.com website or from another website.