1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Navigate between pages using option element

Discussion in 'VBA Macros' started by YasserKhalil, Sep 11, 2017.

  1. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Hello everyone
    I am practicing on web scraping and I have this try to grab data from 57 pages .. I am trying to use option tags to navigate between pages ..
    The code only grabs the first page 57 times
    Code (vb):
    Sub Test()
        Dim xml As Object
        Dim html As Object
        Dim objTable As Object
        Dim result As String
        Dim lRow As Long
        Dim lngTable As Long
        Dim lngRow As Long
        Dim lngCol As Long
        Dim ActRw As Long
        Dim htmlOptions As MSHTML.IHTMLElementCollection
        Dim htmlOption As MSHTML.IHTMLElement

        Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
        With xml
            .Open "GET", "http://registers.centralbank.ie/FundSearchResultsPage.aspx?searchEntity=FundServiceProvider&searchType=Name&searchText=&registers=6%2c29%2c44%2c45", False
            .send
        End With
        result = xml.responseText
        Set html = CreateObject("htmlfile")
        html.body.innerHTML = result


        Set htmlOptions = html.getElementsByTagName("option")

        For Each htmlOption In htmlOptions
            With htmlOption
                htmlOption.Click
                xml.Open "GET", "http://registers.centralbank.ie/FundSearchResultsPage.aspx?searchEntity=FundServiceProvider&searchType=Name&searchText=&registers=6%2c29%2c44%2c45", False
                xml.send
           
                result = xml.responseText
                Set html = CreateObject("htmlfile")
                html.body.innerHTML = result

                Set objTable = html.getElementsByTagName("table")

                For lngTable = 1 To objTable.Length - 1
                    For lngRow = 1 To objTable(lngTable).Rows.Length - 2
                        For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                            ThisWorkbook.Sheets("Sheet1").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
                        Next lngCol
                    Next lngRow
                    ActRw = ActRw + objTable(lngTable).Rows.Length + 1
                Next lngTable
            End With
        Next htmlOption
    End Sub
    Any help please
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    Page is loaded by JavaScript. You can't scrape it like that using XML and HTTP.

    Looking at how site loads data, easiest method is to use IE automation or Selenium.

    You need navigate to URL, then wait for load. Then identify use getElementsByID on "ctl00_cphRegistersMasterPage_gvwSearchResults_ctl18_ddlPages".
    Then for each child object in that element, set selected to TRUE and and wait for load and loop.

    Scraping data during each iteration of the loop.

    Alternately, you can probably use click on "ctl00_cphRegistersMasterPage_gvwSearchResults_ctl18_btnNext" to loop.
    Chirag R Raval and shahin like this.
  3. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thanks a lot for reply. But why it is grabbing the first page ..
    Isn't there a way to use XML and HTTP to loop through the pages ..
    I tried to look at the URL but didn't find anu suitable part to change the page from ..
    And using IE will be slower
  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,277
    Hi !

    When you manually load second page, just inspect the
    request used by your webbrowser (scrapping basics !) …

    Do the same for third page and compare both : you should catch it !
    shahin likes this.
  5. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thanks a lot Mr. Marc for reply
    Can you guide me with a link to learn how to inspect the request ..?
    In fact I have no idea about what do you mean exactly .. I have inspected the drop down but couldn't get a clue
    I have looped option elements and it gave me 57 numbers .. But the question is how to navigate using this xml method to the 57 pages?
  6. Marc L

    Marc L Excel Ninja

    Messages:
    3,277
    shahin likes this.
  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    It's going to be pain in the rear to scrape this site using XMLHttp.

    You can see where ddlPages is set at end of query string. However, this site uses viewstate and other properties that need to be included in query string.
    upload_2017-9-11_13-29-31.png
    shahin likes this.
  8. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thanks a lot for replies ..and for these useful links
    So there is no way to do that using XML ?! or there is a way but will be difficult
    I still can't figure it out
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    There is way. See the request string in the screen shot.
    You just need to parse that monster of string and just change the last parameter in each loop iteration.
    Chirag R Raval likes this.
  10. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thanks a lot for reply Chihiro .. Forgive me .. I don't know how you snapshot your screen shot and I couldn't find that monster string ..Can you guide me please?
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    On your browser, go to Tool and locate Developer tools (Ctrl + SHIFT + I for chrome).

    Set it to record and retain and navigate to Netowrk tab. There locate "All".

    Now, navigate to site and observe what happens when you change page.

    You should see "FundSearchResultsPage.aspx....". Click on it.

    Now, on right hand panel, you will see "Headers" navigate down on that tab, find "Form Data" section.

    There you will see the request string "POST"ed to get the response. However, since it's been parsed, click on "view source" to get the raw string.

    Read through 1st and 2nd link provided by Marc and you should find more general tutorial on developer tool use.

    EDIT: Since, it's using POST method, you may need to set few headers play around with it and test what is required.
    YasserKhalil likes this.
  12. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thank you very much for great information. I will have a look at those links again to learn more. Thanks for guiding me
  13. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Will I have to use this monster string in looping .. Is there any other escape way?
  14. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    Just store it as variable and use that variable in loop.
  15. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thanks a lot. Will it be inside the loop only in the place of url ..? or in both strings in the code
  16. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    YasserKhalil likes this.
  17. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Will I start with this string "__EVENTTARGET=" or any other string .. I am confused and don't know how will I split this monster string ..

    Attached Files:

  18. Marc L

    Marc L Excel Ninja

    Messages:
    3,277


    So the easier for you is to pilot IE …​
  19. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thanks .. But what about the speed point?
  20. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,919
    With just 57 pages, it's way easier to use IE. Though it's slower, less hassle in coding.

    Now if it was 10k + pages. Then definitely spend time to code. Since, each load will take about 5 sec...
  21. shahin

    shahin Active Member

    Messages:
    601
    Hey, give this a shot. It will give you the names from the first page.
    Code (vb):

    Sub central_bank()

        Dim http As New XMLHTTP60, html As New HTMLDocument
        Dim ArgumentStr As String, post As Object

        ArgumentStr = "__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__VIEWSTATE=%2FwEPDwUKLTEwMjc3MzEyMw9kFgJmD2QWAgIDD2QWBAIRDw8WAh4EVGV4dAUHR2FlaWxnZRYIHgtvbm1vdXNlb3ZlcgUzdGhpcy5zdHlsZS5jb2xvcj0nIzk3MjAwYSc7dGhpcy5zdHlsZS5jdXJzb3I9J2hhbmQnHgdvbmZvY3VzBTB0aGlzLnN0eWxlLmNvbG9yPScjMDAwJzt0aGlzLnN0eWxlLmN1cnNvcj0naGFuZCceCm9ubW91c2VvdXQFF3RoaXMuc3R5bGUuY29sb3I9JyMwMDAnHgZvbmJsdXIFF3RoaXMuc3R5bGUuY29sb3I9JyMwMDAnZAITD2QWAgIZDxQrAA0PFggeE0F1dG9HZW5lcmF0ZUNvbHVtbnNoHgtfIURhdGFCb3VuZGceC18hSXRlbUNvdW50AtMGHgxBbGxvd1NvcnRpbmdnFgIeB3N1bW1hcnkFc0Rpc3BsYXlzIGEgbGlzdCBvZiBmdW5kcyBhbmQgbGlua3MgZWFjaCBmdW5kcyBkZXRhaWxzIGFuZCB0aGUgdHJ1c3RlZSBhbmQgbWFuYWdlbWVudCBjb21wYW5p" & _
        "ZXMgZmlybSBhdXRob3Jpc2F0aW9ucy4QFgNmAgECAhYDFCsABRYKHghSZWFkT25seWceCkhlYWRlclRleHQFB1JlZiBOby4eCURhdGFGaWVsZAUPUmVmZXJlbmNlTnVtYmVyHg5Tb3J0RXhwcmVzc2lvbgUPUmVmZXJlbmNlTnVtYmVyHgpGb290ZXJUZXh0BQNBU0MWBB4IQ3NzQ2xhc3MFCWd2d0NvbHVtbh4EXyFTQgICZGRkFCsABRYMHg1EYXRhVGV4dEZpZWxkBQROYW1lHwsFCUZpcm0gTmFtZR4VRGF0YU5hdmlnYXRlVXJsRmllbGRzFQEPUmVmZXJlbmNlTnVtYmVyHhtEYXRhTmF2aWdhdGVVcmxGb3JtYXRTdHJpbmcFPUZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9ezB9JnJlZ2lzdGVyPTYsMjksNDQs" & _
        "NDUfDQUETmFtZR8OBQNBU0MWBB8PBRBlbnRpdHlOYW1lQ29sdW1uHxACAmRkZBQrAAUWCh8KZx8LBQxUcmFkaW5nIE5hbWUfDAULVHJhZGluZ05hbWUfDQULVHJhZGluZ05hbWUfDgUDQVNDFgQfDwUXZW50aXR5VHJhZGluZ05hbWVDb2x1bW4fEAICZGRkFgNmAgRmFgQfDwUSc2VhcmNocmVzdWx0c3BhZ2VyHxACAhYGHw8FE3NlYXJjaHJlc3VsdHNoZWFkZXIeBFdyYXBoHxACgoAQZGRkZGQWDB4IUG9zaXRpb24LKidTeXN0ZW0uV2ViLlVJLldlYkNvbnRyb2xzLlBhZ2VyUG9zaXRpb24AHglQYWdlck1vZGULKiZTeXN0ZW0uV2ViLlVJLldlYkNvbnRyb2xzLlBhZ2VyQnV0dG9ucwIeDE5leHRQYWdlVGV4dAUETmV4dB4QUHJldmlvdXNQYWdlVGV4dAUIUHJldmlvdXMeDUZpcnN0UGFnZVRleHQFBUZpcnN0HgxMYXN0UGFnZVRleHQFBExhc3RkFgYeCUdyaWRMaW5lcwsqI1N5c3RlbS5XZWIuVUkuV2ViQ29udHJvbHMuR3JpZExpbmVzAR8PBQ1zZWFyY2hy" & _
        "ZXN1bHRzHxACgoAgZBYCZg9kFiICAQ9kFgZmDw8WAh8ABQdDMTU3MDA4ZGQCAQ9kFgJmDw8WBB8ABQ4xNyBDYXBpdGFsIExMUB4LTmF2aWdhdGVVcmwFQUZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzE1NzAwOCZyZWdpc3Rlcj02LDI5LDQ0LDQ1ZGQCAg8PFgIfAAUGJm5ic3A7ZGQCAg9kFgZmDw8WAh8ABQZDNTgxMjJkZAIBD2QWAmYPDxYEHwAFHTM2IFNvdXRoIENhcGl0YWwgQWR2aXNvcnMgTExQHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzU4MTIyJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABR0zNiBTb3V0aCBDYXBpdGFsIEFkdmlzb3JzIExMUGRkAgMPZ" & _
        "BYGZg8PFgIfAAUGQzY4MjM4ZGQCAQ9kFgJmDw8WBB8ABS4zNiBTb3V0aCBJbnZlc3RtZW50IE1hbmFnZXJzIChJcmVsYW5kKSBMaW1pdGVkHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzY4MjM4JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABS4zNiBTb3V0aCBJbnZlc3RtZW50IE1hbmFnZXJzIChJcmVsYW5kKSBMaW1pdGVkZGQCBA9kFgZmDw8WAh8ABQdDMTMyMTcyZGQCAQ9kFgJmDw8WBB8ABRIzaSBJbnZlc3RtZW50" & _
        "cyBwbGMfHAVBRmlybURhdGFQYWdlLmFzcHg%2FZmlybVJlZmVyZW5jZU51bWJlcj1DMTMyMTcyJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIFD2QWBmYPDxYCHwAFBkMyODk5NGRkAgEPZBYCZg8PFgQfAAUfNEQgR2xvYmFsIEVuZXJneSBBZHZpc29ycywgU0FTLh8cBUBGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMyODk5NCZyZWdpc3Rlcj02LDI5LDQ0LDQ1ZGQCAg8PFgIfAAUeNEQgR2xvYmFsIEVuZXJneSBBZHZpc29ycywgU0FTZGQCBg9kFgZmDw8WAh8ABQdDMTM4NDg0ZGQCAQ9kFgJmDw8WBB8ABRNBYWx0byBJbnZlc3QgVUsgTHRkHxwFQUZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzEzODQ4NCZyZWdpc3Rlcj02LDI5LDQ0LDQ1ZGQCAg8PFgIfAAUGJm5ic3A7ZGQCBw9kFgZmDw8WAh8ABQZDNDg0MzFkZAIBD2QWAmYPDxYEHwAFE0FBTSBBbHBoYSBGdW5kcyBwbGMfHAVARmlybURhdGFQYWdlLmFzcHg%2FZmlybVJlZmVyZW5jZU51bWJlcj1DNDg0MzEmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAgIPDxYCHwAFE0FBT" & _
        "SBBbHBoYSBGdW5kcyBwbGNkZAIID2QWBmYPDxYCHwAFBkMyODIwMWRkAgEPZBYCZg8PFgQfAAUVQWJiZXkgQ2FwaXRhbCBMaW1pdGVkHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzI4MjAxJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIJD2QWBmYPDxYCHwAFBkM0NDI4N2RkAgEPZBYCZg8PFgQfAAUeQUJDIEFyYml0cmFnZSBBc3NldCBNYW5hZ2VtZW50HxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzQ0Mjg3JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABR5BQkMgQXJiaXRyYWdlIEFzc2V0IE1hbmFnZW1lbnRkZAIKD2QWBmYPDxYCHwAFBkM2NTY0N2RkAgEPZBYCZg8PFgQfAAUkQUJFUkRFRU4gQVNTRVQgTUFOQUdFTUVOVCBDQVlNQU4gTFREHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzY1NjQ3JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAILD2QWBmYPDxYCHwAFB0MxNjM0MDVkZAIBD2QWAmYPDxYEHwAFH0FiZXJkZWVuIENhcGl0YWwg" & _
        "TWFuYWdlbWVudCBMTEMfHAVBRmlybURhdGFQYWdlLmFzcHg%2FZmlybVJlZmVyZW5jZU51bWJlcj1DMTYzNDA1JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIMD2QWBmYPDxYCHwAFBkMyMDg2MmRkAgEPZBYCZg8PFgQfAAUqQWJlcmRlZW4gRnVuZCBNYW5hZ2VtZW50IChJcmVsYW5kKSBMaW1pdGVkHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzIwODYyJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIND2QWBmYPDxYCHwAFB0MxNDQ1NjVkZAIBD2QWAmYPDxYEHwAFHkFiZXJkZWVuIEZ1bmQgTWFuYWdlcnMgTGltaXRlZB8cBUFGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMxNDQ1NjUmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAgIPDxYCHwAFBiZuYnNwO2RkAg4PZBYGZg8PFgIfAAUHQzEzNzU5N2RkAgEPZBYCZg8PFgQfAAUcQWJlcmRlZW4gR2xvYmFsIFNlcnZpY2VzIFMuQR8cBUFGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMxMzc1OTcmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAg" & _
        "IPDxYCHwAFBiZuYnNwO2RkAg8PZBYGZg8PFgIfAAUHQzE3MTY4MWRkAgEPZBYCZg8PFgQfAAUWQWJlcmZvcnRoIFBhcnRuZXJzIExMUB8cBUFGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMxNzE2ODEmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAgIPDxYCHwAFBiZuYnNwO2RkAhAPDxYCHgdWaXNpYmxlaGRkAhEPZBYCZg9kFggCAQ8PFgIeB0VuYWJsZWRoZGQCAw8PFgIfHmhkZAIFDxAPFgIfBmdkDx" & _
        "Y5ZgIBAgICAwIEAgUCBgIHAggCCQIKAgsCDAINAg4CDwIQAhECEgITAhQCFQIWAhcCGAIZAhoCGwIcAh0CHgIfAiACIQIiAiMCJAIlAiYCJwIoAikCKgIrAiwCLQIuAi8CMAIxAjICMwI0AjUCNgI3AjgWORAFATEFATFnEAUBMgUBMmcQBQEzBQEzZxAFATQFATRnEAUBNQUBNWcQBQE2BQE2ZxAFATcFATdnEAUBOAUBOGcQBQE5BQE5ZxAFAjEwBQIxMGcQBQIxMQUCMTFnEAUCMTIFAjEyZxAFAjEzBQIxM2cQBQIxNAUCMTRnEAUCMTUFAjE1ZxAFAjE2BQIxNmcQBQIxNwUCMTdnEAUCMTgFAjE4ZxAFAjE5BQIxOWcQBQIyMAUCMjBnEAUCMjEFAjIxZxAFAjIyBQIyMmcQBQIyMwUCMjNnEAUCMjQFAjI0ZxAFAjI1BQIyNWcQBQIyNgUCMjZnEAUCMjcFAjI3ZxAFAjI4BQIyOGcQBQIyOQUCMjlnEAUCMzAFAjMwZxAFAjMxBQIzMWcQBQIzMgUCMzJnEAUCMzMFAjMzZxAFAjM0BQIzNGcQBQIzNQUCMzVnEAUCMzYFAjM2ZxAFAjM3BQIzN2cQBQIzOAUCMzhnEAUCMzkFAjM5ZxAFAjQwBQI0MGcQBQI0MQUCNDFnEAUCNDIFAjQyZxAFAjQzBQI0M2cQBQI0NAUCNDRnEAUCNDUFAjQ1ZxAFAj" & _
        "Q2BQI0NmcQBQI0NwUCNDdnEAUCNDgFAjQ4ZxAFAjQ5BQI0OWcQBQI1MAUCNTBnEAUCNTEFAjUxZxAFAjUyBQI1MmcQBQI1MwUCNTNnEAUCNTQFAjU0ZxAFAjU1BQI1NWcQBQI1NgUCNTZnEAUCNTcFAjU3ZxYBZmQCBw8PFgIfAAUCNTdkZBgCBR5fX0NvbnRyb2xzUmVxdWlyZVBvc3RCYWNrS2V5X18WBAU8Y3RsMDAkY3BoUmVnaXN0ZXJzTWFzdGVyUGFnZSRndndTZWFyY2hSZXN1bHRzJGN0bDE4JGJ0bkZpcnN0BTtjdGwwMCRjcGhSZWdpc3RlcnNNYXN0ZXJQYWdlJGd2d1NlYXJjaFJlc3VsdHMkY3RsMTgkYnRuUHJldgU7Y3RsMDAkY3BoUmVnaXN0ZXJzTWFzdGVyUGFnZSRndndTZWFyY2hSZXN1bHRzJGN0bDE4JGJ0bk5leHQFO2N0bDAwJGNwaFJlZ2lzdGVyc01hc3RlclBhZ2UkZ3Z3U2VhcmNoUmVzdWx0cyRjdGwxOCRidG5MYXN0BS1jdGwwMCRjcGhSZWdpc3RlcnNNYXN0ZXJQYWdlJGd2d1NlYXJjaFJlc3VsdHMPPCsACgEIAjlkQ3RZxUBq4maQklzpY%2Fiz0lRdFTg%3D&__SCROLLPOSITIONX=0&__SCROLLPOSITIONY=540&__EVENTVALIDATION=%2FwEWQwLhyt7aAQKdmL%2BNBQLBvq75AwKEl773" & _
        "CgL%2B4cLqBQLR7%2FSRCgKrq7r4AgKnifiNCgKo5tLjBgKp5tLjBgKq5tLjBgKr5tLjBgKs5tLjBgKt5tLjBgKu5tLjBgK%2F5tLjBgKw5tLjBgKo5pLgBgKo5p7gBgKo5prgBgKo5qbgBgKo5qLgBgKo5q7gBgKo5qrgBgKo5rbgBgKo5vLjBgKo5v7jBgKp5pLgBgKp5p7gBgKp5prgBgKp5qbgBgKp5qLgBgKp5q7gBgKp5qrgBgKp5rbgBgKp5vLjBgKp5v7jBgKq5pLgBgKq5p7gBgKq5prgBgKq5qbgBgKq5qLgBgKq5q7gBgKq5qrgBgKq5rbgBgKq5vLjBgKq5v7jBgKr5pLgBgKr5p7gBgKr5prgBgKr5qbgBgKr5qLgBgKr5q7gBgKr5qrgBgKr5rbgBgKr5vLjBgKr5v7jBgKs5pLgBgKs5p7gBgKs5prgBgKs5qbgBgKs5qLgBgKs5q7gBgKs5qrgBgKs5rbgBgLg2MrQBwLk2LbQCBjiuH1rQsNKIEFR7v2fYvVvD8aB&ctl00%24cphRegistersMasterPage%24gvwSearchResults%24ctl18%24ddlPages=1&ctl00%24cphRegistersMasterPage%24gvwSearchResults%24ctl18%24btnNext.x=262&ctl00%24cphRegistersMasterPage%24gvwSearchResults%24ctl18%24btnNext.y=6"

        With http
            .Open "POST", "http://registers.centralbank.ie/FundSearchResultsPage.aspx?searchEntity=FundServiceProvider&searchType=Name&searchText=&registers=6%2c29%2c44%2c45&AspxAutoDetectCookieSupport=1", False
            .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
            .send ArgumentStr
            html.body.innerHTML = .responseText
        End With
        For Each post In html.getElementsByClassName("entityNameColumn")
            With post.getElementsByTagName("a")
                If .Length Then x = x + 1: Cells(x, 1) = post.innerText
            End With
        Next post
     
    End Sub
     
    Thanks to sir Chihiro for showing me how to break such giant string into a manageable chunk.
    YasserKhalil likes this.
  22. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    Thanks a lot Mr. Shahin.
    As for results I got just one column. It is supposed there are three columns
    And how can I loop .. I didn't find a clue for a page number
  23. shahin

    shahin Active Member

    Messages:
    601
    Just tell me what you wanna get as results from its first page. I just tried to show that it works. Specify it clearly so that i can do it for you.
  24. shahin

    shahin Active Member

    Messages:
    601
    Is this what you wanted?
    Code (vb):

    Sub central_bank()

        Dim http As New XMLHTTP60, html As New HTMLDocument
        Dim tbl As Object, tRow As Object, tCel As Object
        Dim ArgumentStr As String
        ArgumentStr = "__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__VIEWSTATE=%2FwEPDwUKLTEwMjc3MzEyMw9kFgJmD2QWAgIDD2QWBAIRDw8WAh4EVGV4dAUHR2FlaWxnZRYIHgtvbm1vdXNlb3ZlcgUzdGhpcy5zdHlsZS5jb2xvcj0nIzk3MjAwYSc7dGhpcy5zdHlsZS5jdXJzb3I9J2hhbmQnHgdvbmZvY3VzBTB0aGlzLnN0eWxlLmNvbG9yPScjMDAwJzt0aGlzLnN0eWxlLmN1cnNvcj0naGFuZCceCm9ubW91c2VvdXQFF3RoaXMuc3R5bGUuY29sb3I9JyMwMDAnHgZvbmJsdXIFF3RoaXMuc3R5bGUuY29sb3I9JyMwMDAnZAITD2QWAgIZDxQrAA0PFggeE0F1dG9HZW5lcmF0ZUNvbHVtbnNoHgtfIURhdGFCb3VuZGceC18hSXRlbUNvdW50AtMGHgxBbGxvd1NvcnRpbmdnFgIeB3N1bW1hcnkFc0Rpc3BsYXlzIGEgbGlzdCBvZiBmdW5kcyBhbmQgbGlua3MgZWFjaCBmdW5kcyBkZXRhaWxzIGFuZCB0aGUgdHJ1c3RlZSBhbmQgbWFuYWdlbWVudCBjb21wYW5p" & _
        "ZXMgZmlybSBhdXRob3Jpc2F0aW9ucy4QFgNmAgECAhYDFCsABRYKHghSZWFkT25seWceCkhlYWRlclRleHQFB1JlZiBOby4eCURhdGFGaWVsZAUPUmVmZXJlbmNlTnVtYmVyHg5Tb3J0RXhwcmVzc2lvbgUPUmVmZXJlbmNlTnVtYmVyHgpGb290ZXJUZXh0BQNBU0MWBB4IQ3NzQ2xhc3MFCWd2d0NvbHVtbh4EXyFTQgICZGRkFCsABRYMHg1EYXRhVGV4dEZpZWxkBQROYW1lHwsFCUZpcm0gTmFtZR4VRGF0YU5hdmlnYXRlVXJsRmllbGRzFQEPUmVmZXJlbmNlTnVtYmVyHhtEYXRhTmF2aWdhdGVVcmxGb3JtYXRTdHJpbmcFPUZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9ezB9JnJlZ2lzdGVyPTYsMjksNDQs" & _
        "NDUfDQUETmFtZR8OBQNBU0MWBB8PBRBlbnRpdHlOYW1lQ29sdW1uHxACAmRkZBQrAAUWCh8KZx8LBQxUcmFkaW5nIE5hbWUfDAULVHJhZGluZ05hbWUfDQULVHJhZGluZ05hbWUfDgUDQVNDFgQfDwUXZW50aXR5VHJhZGluZ05hbWVDb2x1bW4fEAICZGRkFgNmAgRmFgQfDwUSc2VhcmNocmVzdWx0c3BhZ2VyHxACAhYGHw8FE3NlYXJjaHJlc3VsdHNoZWFkZXIeBFdyYXBoHxACgoAQZGRkZGQWDB4IUG9zaXRpb24LKidTeXN0ZW0uV2ViLlVJLldlYkNvbnRyb2xzLlBhZ2VyUG9zaXRpb24AHglQYWdlck1vZGULKiZTeXN0ZW0uV2ViLlVJLldlYkNvbnRyb2xzLlBhZ2VyQnV0dG9ucwIeDE5leHRQYWdlVGV4dAUETmV4dB4QUHJldmlvdXNQYWdlVGV4dAUIUHJldmlvdXMeDUZpcnN0UGFnZVRleHQFBUZpcnN0HgxMYXN0UGFnZVRleHQFBExhc3RkFgYeCUdyaWRMaW5lcwsqI1N5c3RlbS5XZWIuVUkuV2ViQ29udHJvbHMuR3JpZExpbmVzAR8PBQ1zZWFyY2hy" & _
        "ZXN1bHRzHxACgoAgZBYCZg9kFiICAQ9kFgZmDw8WAh8ABQdDMTU3MDA4ZGQCAQ9kFgJmDw8WBB8ABQ4xNyBDYXBpdGFsIExMUB4LTmF2aWdhdGVVcmwFQUZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzE1NzAwOCZyZWdpc3Rlcj02LDI5LDQ0LDQ1ZGQCAg8PFgIfAAUGJm5ic3A7ZGQCAg9kFgZmDw8WAh8ABQZDNTgxMjJkZAIBD2QWAmYPDxYEHwAFHTM2IFNvdXRoIENhcGl0YWwgQWR2aXNvcnMgTExQHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzU4MTIyJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABR0zNiBTb3V0aCBDYXBpdGFsIEFkdmlzb3JzIExMUGRkAgMPZ" & _
        "BYGZg8PFgIfAAUGQzY4MjM4ZGQCAQ9kFgJmDw8WBB8ABS4zNiBTb3V0aCBJbnZlc3RtZW50IE1hbmFnZXJzIChJcmVsYW5kKSBMaW1pdGVkHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzY4MjM4JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABS4zNiBTb3V0aCBJbnZlc3RtZW50IE1hbmFnZXJzIChJcmVsYW5kKSBMaW1pdGVkZGQCBA9kFgZmDw8WAh8ABQdDMTMyMTcyZGQCAQ9kFgJmDw8WBB8ABRIzaSBJbnZlc3RtZW50" & _
        "cyBwbGMfHAVBRmlybURhdGFQYWdlLmFzcHg%2FZmlybVJlZmVyZW5jZU51bWJlcj1DMTMyMTcyJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIFD2QWBmYPDxYCHwAFBkMyODk5NGRkAgEPZBYCZg8PFgQfAAUfNEQgR2xvYmFsIEVuZXJneSBBZHZpc29ycywgU0FTLh8cBUBGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMyODk5NCZyZWdpc3Rlcj02LDI5LDQ0LDQ1ZGQCAg8PFgIfAAUeNEQgR2xvYmFsIEVuZXJneSBBZHZpc29ycywgU0FTZGQCBg9kFgZmDw8WAh8ABQdDMTM4NDg0ZGQCAQ9kFgJmDw8WBB8ABRNBYWx0byBJbnZlc3QgVUsgTHRkHxwFQUZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzEzODQ4NCZyZWdpc3Rlcj02LDI5LDQ0LDQ1ZGQCAg8PFgIfAAUGJm5ic3A7ZGQCBw9kFgZmDw8WAh8ABQZDNDg0MzFkZAIBD2QWAmYPDxYEHwAFE0FBTSBBbHBoYSBGdW5kcyBwbGMfHAVARmlybURhdGFQYWdlLmFzcHg%2FZmlybVJlZmVyZW5jZU51bWJlcj1DNDg0MzEmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAgIPDxYCHwAFE0FBT" & _
        "SBBbHBoYSBGdW5kcyBwbGNkZAIID2QWBmYPDxYCHwAFBkMyODIwMWRkAgEPZBYCZg8PFgQfAAUVQWJiZXkgQ2FwaXRhbCBMaW1pdGVkHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzI4MjAxJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIJD2QWBmYPDxYCHwAFBkM0NDI4N2RkAgEPZBYCZg8PFgQfAAUeQUJDIEFyYml0cmFnZSBBc3NldCBNYW5hZ2VtZW50HxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzQ0Mjg3JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABR5BQkMgQXJiaXRyYWdlIEFzc2V0IE1hbmFnZW1lbnRkZAIKD2QWBmYPDxYCHwAFBkM2NTY0N2RkAgEPZBYCZg8PFgQfAAUkQUJFUkRFRU4gQVNTRVQgTUFOQUdFTUVOVCBDQVlNQU4gTFREHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzY1NjQ3JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAILD2QWBmYPDxYCHwAFB0MxNjM0MDVkZAIBD2QWAmYPDxYEHwAFH0FiZXJkZWVuIENhcGl0YWwg" & _
        "TWFuYWdlbWVudCBMTEMfHAVBRmlybURhdGFQYWdlLmFzcHg%2FZmlybVJlZmVyZW5jZU51bWJlcj1DMTYzNDA1JnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIMD2QWBmYPDxYCHwAFBkMyMDg2MmRkAgEPZBYCZg8PFgQfAAUqQWJlcmRlZW4gRnVuZCBNYW5hZ2VtZW50IChJcmVsYW5kKSBMaW1pdGVkHxwFQEZpcm1EYXRhUGFnZS5hc3B4P2Zpcm1SZWZlcmVuY2VOdW1iZXI9QzIwODYyJnJlZ2lzdGVyPTYsMjksNDQsNDVkZAICDw8WAh8ABQYmbmJzcDtkZAIND2QWBmYPDxYCHwAFB0MxNDQ1NjVkZAIBD2QWAmYPDxYEHwAFHkFiZXJkZWVuIEZ1bmQgTWFuYWdlcnMgTGltaXRlZB8cBUFGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMxNDQ1NjUmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAgIPDxYCHwAFBiZuYnNwO2RkAg4PZBYGZg8PFgIfAAUHQzEzNzU5N2RkAgEPZBYCZg8PFgQfAAUcQWJlcmRlZW4gR2xvYmFsIFNlcnZpY2VzIFMuQR8cBUFGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMxMzc1OTcmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAg" & _
        "IPDxYCHwAFBiZuYnNwO2RkAg8PZBYGZg8PFgIfAAUHQzE3MTY4MWRkAgEPZBYCZg8PFgQfAAUWQWJlcmZvcnRoIFBhcnRuZXJzIExMUB8cBUFGaXJtRGF0YVBhZ2UuYXNweD9maXJtUmVmZXJlbmNlTnVtYmVyPUMxNzE2ODEmcmVnaXN0ZXI9NiwyOSw0NCw0NWRkAgIPDxYCHwAFBiZuYnNwO2RkAhAPDxYCHgdWaXNpYmxlaGRkAhEPZBYCZg9kFggCAQ8PFgIeB0VuYWJsZWRoZGQCAw8PFgIfHmhkZAIFDxAPFgIfBmdkDx" & _
        "Y5ZgIBAgICAwIEAgUCBgIHAggCCQIKAgsCDAINAg4CDwIQAhECEgITAhQCFQIWAhcCGAIZAhoCGwIcAh0CHgIfAiACIQIiAiMCJAIlAiYCJwIoAikCKgIrAiwCLQIuAi8CMAIxAjICMwI0AjUCNgI3AjgWORAFATEFATFnEAUBMgUBMmcQBQEzBQEzZxAFATQFATRnEAUBNQUBNWcQBQE2BQE2ZxAFATcFATdnEAUBOAUBOGcQBQE5BQE5ZxAFAjEwBQIxMGcQBQIxMQUCMTFnEAUCMTIFAjEyZxAFAjEzBQIxM2cQBQIxNAUCMTRnEAUCMTUFAjE1ZxAFAjE2BQIxNmcQBQIxNwUCMTdnEAUCMTgFAjE4ZxAFAjE5BQIxOWcQBQIyMAUCMjBnEAUCMjEFAjIxZxAFAjIyBQIyMmcQBQIyMwUCMjNnEAUCMjQFAjI0ZxAFAjI1BQIyNWcQBQIyNgUCMjZnEAUCMjcFAjI3ZxAFAjI4BQIyOGcQBQIyOQUCMjlnEAUCMzAFAjMwZxAFAjMxBQIzMWcQBQIzMgUCMzJnEAUCMzMFAjMzZxAFAjM0BQIzNGcQBQIzNQUCMzVnEAUCMzYFAjM2ZxAFAjM3BQIzN2cQBQIzOAUCMzhnEAUCMzkFAjM5ZxAFAjQwBQI0MGcQBQI0MQUCNDFnEAUCNDIFAjQyZxAFAjQzBQI0M2cQBQI0NAUCNDRnEAUCNDUFAjQ1ZxAFAj" & _
        "Q2BQI0NmcQBQI0NwUCNDdnEAUCNDgFAjQ4ZxAFAjQ5BQI0OWcQBQI1MAUCNTBnEAUCNTEFAjUxZxAFAjUyBQI1MmcQBQI1MwUCNTNnEAUCNTQFAjU0ZxAFAjU1BQI1NWcQBQI1NgUCNTZnEAUCNTcFAjU3ZxYBZmQCBw8PFgIfAAUCNTdkZBgCBR5fX0NvbnRyb2xzUmVxdWlyZVBvc3RCYWNrS2V5X18WBAU8Y3RsMDAkY3BoUmVnaXN0ZXJzTWFzdGVyUGFnZSRndndTZWFyY2hSZXN1bHRzJGN0bDE4JGJ0bkZpcnN0BTtjdGwwMCRjcGhSZWdpc3RlcnNNYXN0ZXJQYWdlJGd2d1NlYXJjaFJlc3VsdHMkY3RsMTgkYnRuUHJldgU7Y3RsMDAkY3BoUmVnaXN0ZXJzTWFzdGVyUGFnZSRndndTZWFyY2hSZXN1bHRzJGN0bDE4JGJ0bk5leHQFO2N0bDAwJGNwaFJlZ2lzdGVyc01hc3RlclBhZ2UkZ3Z3U2VhcmNoUmVzdWx0cyRjdGwxOCRidG5MYXN0BS1jdGwwMCRjcGhSZWdpc3RlcnNNYXN0ZXJQYWdlJGd2d1NlYXJjaFJlc3VsdHMPPCsACgEIAjlkQ3RZxUBq4maQklzpY%2Fiz0lRdFTg%3D&__SCROLLPOSITIONX=0&__SCROLLPOSITIONY=540&__EVENTVALIDATION=%2FwEWQwLhyt7aAQKdmL%2BNBQLBvq75AwKEl773" & _
        "CgL%2B4cLqBQLR7%2FSRCgKrq7r4AgKnifiNCgKo5tLjBgKp5tLjBgKq5tLjBgKr5tLjBgKs5tLjBgKt5tLjBgKu5tLjBgK%2F5tLjBgKw5tLjBgKo5pLgBgKo5p7gBgKo5prgBgKo5qbgBgKo5qLgBgKo5q7gBgKo5qrgBgKo5rbgBgKo5vLjBgKo5v7jBgKp5pLgBgKp5p7gBgKp5prgBgKp5qbgBgKp5qLgBgKp5q7gBgKp5qrgBgKp5rbgBgKp5vLjBgKp5v7jBgKq5pLgBgKq5p7gBgKq5prgBgKq5qbgBgKq5qLgBgKq5q7gBgKq5qrgBgKq5rbgBgKq5vLjBgKq5v7jBgKr5pLgBgKr5p7gBgKr5prgBgKr5qbgBgKr5qLgBgKr5q7gBgKr5qrgBgKr5rbgBgKr5vLjBgKr5v7jBgKs5pLgBgKs5p7gBgKs5prgBgKs5qbgBgKs5qLgBgKs5q7gBgKs5qrgBgKs5rbgBgLg2MrQBwLk2LbQCBjiuH1rQsNKIEFR7v2fYvVvD8aB&ctl00%24cphRegistersMasterPage%24gvwSearchResults%24ctl18%24ddlPages=1&ctl00%24cphRegistersMasterPage%24gvwSearchResults%24ctl18%24btnNext.x=262&ctl00%24cphRegistersMasterPage%24gvwSearchResults%24ctl18%24btnNext.y=6"

        With http
            .Open "POST", "http://registers.centralbank.ie/FundSearchResultsPage.aspx?searchEntity=FundServiceProvider&searchType=Name&searchText=&registers=6%2c29%2c44%2c45&AspxAutoDetectCookieSupport=1", False
            .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
            .send ArgumentStr
            html.body.innerHTML = .responseText
        End With
        Set tbl = html.getElementsByTagName("table")(1)
        For Each tRow In tbl.getElementsByTagName("tr")
            For Each tCel In tRow.getElementsByTagName("td")
                If Not InStr(tCel.innerText, "Page") > 0 Then
                    c = c + 1: Cells(x + 1, c) = tCel.innerText
                End If
            Next tCel
            c = 0
            x = x + 1
        Next tRow
       
    End Sub
     
    YasserKhalil likes this.
  25. YasserKhalil

    YasserKhalil Active Member

    Messages:
    774
    That's great and awesome. Now how can be looped through the 57 pages? I looked at the string and didn't find a clue to page number

Share This Page