XMLHTTP navigation and obtaining table.

Trebor8484

Board Regular
Joined
Oct 27, 2018
Messages
57
Hi all,

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

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,038
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top