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.

How to scroll to the bottom of a webpage using vba?

Discussion in 'VBA Macros' started by shahin, Jul 5, 2017.

  1. shahin

    shahin Active Member

    Messages:
    349
    Hi there everyone!! Hope you all are doing fine. I got a problem accomplishing my little piece of code which is slightly different from what I usually ask for. I've used javascript function within vba code which is written in combination with selenium. I tried to scroll to the downmost portion of a lazy-loading webpage using the below code. However, when my script attempts to do so, an error pops up indicating "object required". I need to get to the bottom of that page. In every scroll, it displays 20 links. There are 1000 links in that page. How the loop of my script should be to do the trick? Here is the code I've written so far:

    Code (vb):

    Sub Testing_scroll()

    Dim driver As New WebDriver

    driver.Start "chrome", "http://fortune.com/fortune500"
    driver.get "/list/"

    Do Until x = 1000
        x = driver.ExecuteScript("window.scrollTo(0, document.body.scrollHeight);").Length
    Loop

    End Sub
     
    Last edited: Jul 5, 2017
  2. shahin

    shahin Active Member

    Messages:
    349
    Tried this way but still no luck:

    Code (vb):

    Sub Testing_scroll()

    Dim driver As New WebDriver
    Dim posts As Object, post As Object

    driver.Start "chrome", "http://fortune.com/fortune500"
    driver.get "/list/"

    Count = 1

    Do Until Count = 30
            driver.ExecuteScript ("window.scrollTo(0, document.body.scrollHeight);")
        Count = Count + 1
    Loop

    End Sub
     
    To be more specific:- All I want is make the loop take place 30 times. However, it loops once and then breaks.
  3. shahin

    shahin Active Member

    Messages:
    349
    I've already made it. Now it gives me all 1000 links from that page. At this point I'm seriously confused why and how this happened. The page displays it's content 20 in each scroll. However, why the figure should be 200 in the loop to get 1000 links I really can't understand. I knew so far that if x = 0 to 10 then the loop will roll 10 times but in this case the math looks weird. Can somebody help me understand why it is happening?

    Code (vb):

    Sub Testscroll()

    Dim driver As New WebDriver
    Dim posts As Object, post As Object

    driver.Start "chrome", "http://fortune.com/fortune500"
    driver.get "/list/"

    driver.Wait 500

    For x = 0 To 200
        driver.ExecuteScript ("window.scrollTo(0, document.body.scrollHeight);")
        driver.Wait 500
    Next x

    Set posts = driver.FindElementsByXPath("//li[contains(concat(' ', @class, ' '), ' small-12 ')]")

    For Each post In posts
        i = i + 1
        Cells(i, 1) = post.FindElementByXPath(".//a").Attribute("href")
    Next post

    End Sub
     
    Last edited: Jul 5, 2017
  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,318
    FYI - It's always good idea to state in the OP, what references are added. Otherwise, we will have to guess at what references are needed to run the module. I assume that you have Selenium added as reference.

    I haven't used Selenium myself. But, it's probably to do with how Selenium executes below script. And not directly related to loop iteration.
    Code (vb):
    driver.ExecuteScript ("window.scrollTo(0, document.body.scrollHeight);")
    ThrottleWorks and shahin like this.
  5. shahin

    shahin Active Member

    Messages:
    349
    Thanks sir chihiro, for your response. Basically I wanted to learn the alternative without hardcoding any number as in, 200 in this case to create a loop until i reach the dead end. Thanks again sir. Btw, It is "Selenium Type Library" what needs to be added to the reference before executing the code.
    Last edited: Jul 5, 2017
    ThrottleWorks likes this.
  6. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,693
    Hi,

    Sorry to interfere. Which reference you are adding to run the macro.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,810
    ThrottleWorks likes this.
  8. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,693
    Hi @NARAYANK991 sir, thanks a lot for the help. I tried with 'selenium' before posting also, but was unable to see the reference. So I posted to re-check name of reference.

    I guess I do not have access to this particular DLL file.
    Have a nice day ahead. :)
  9. Marc L

    Marc L Excel Ninja

    Messages:
    3,050
    Hi !

    selenium does not exist in standard VBA, it's an add-in.

    shahin, for any question about selenium,
    better is to post it on a selenium forum …
  10. shahin

    shahin Active Member

    Messages:
    349
    Thanks Marc L, for your comment. Whatever it is, I always love to see your comment. I'm not a huge fan of selenium either. You perhaps noticed that I've not been creating any new thread where there may be problems meant to be solved by vba + selenium. I'm just trying to resolve the unfinished issues within my previously created different threads. Thanks once again.

Share This Page