Results 1 to 2 of 2

Thread: Help update this Google search code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    479
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help update this Google search code

    Hi

    For the first part my code works fine, it does what it is meant to do.

    1) I place a search item in Sheet2 Cell A1 e.g. "Laptops" and it opens Google and extracts all the url for laptops
    2) It paginates to the next page, for which I have set to 5 pages, so URL from all 5 pages are extracted.
    3) It places all the found urls in to Sheet1

    All this is fine and does not need fixing.

    What I am stuck on and what I need it to do.

    Currently I can only place 1 search keyword at a time, I need the code to run of a list from Sheet2 Column A down to the last row with data in it.

    Column A
    Column B
    Laptops
    Mobile Phones
    Mobile Chargers
    Music Mixer
    Laptop screens

    So the first item "Laptops" is searched for X amount of pages (Currently 5 pages) and then it moves onto the next item in the list and does the same, extracting the urls each time.

    Code:
    Sub webpage()
    
        Dim ie As Object
        Dim htmlDoc As Object
        Dim nextPageElement As Object
        Dim div As Object
        Dim link As Object
        Dim url As String
        Dim pageNumber As Long
        Dim i As Long
        
        ' Takes seach from A1 and places it into google
        url = "https://www.google.co.uk/search?q=" & Replace(Worksheets("Sheet2").Range("A1").Value, " ", "+")
    
    
        Set ie = CreateObject("InternetExplorer.Application")
        
        With ie
            .Visible = True
            .navigate url
            Do While .Busy Or .readyState <> 4
                DoEvents
            Loop
        End With
    
        Application.Wait Now + TimeSerial(0, 0, 5)
        
        Set htmlDoc = ie.document
    
    
        pageNumber = 1
        i = 2
        Do
            For Each div In htmlDoc.getElementsByTagName("div")
                If div.getAttribute("class") = "r" Then
                    Set link = div.getElementsByTagName("a")(0)
                    Cells(i, 2).Value = link.getAttribute("href")
                    i = i + 1
                End If
            Next div
            If pageNumber >= 5 Then Exit Do 'The first 5 pages
            Set nextPageElement = htmlDoc.getElementById("pnnext")
            If nextPageElement Is Nothing Then Exit Do
            
            ' Clicks web next page
            nextPageElement.Click 'next web page
            Do While ie.Busy Or ie.readyState <> 4
                DoEvents
            Loop
            Application.Wait Now + TimeSerial(0, 0, 5)
            Set htmlDoc = ie.document
            pageNumber = pageNumber + 1
        Loop
    
       
    ie.Quit
        Set ie = Nothing
        Set htmlDoc = Nothing
        Set nextPageElement = Nothing
        Set div = Nothing
        Set link = Nothing
    
     MsgBox "All Done"
    
    End Sub
    Thanks for having a look
    If I could code in VB, life wouldn't be such a pain in the A$$

  2. #2
    Board Regular
    Join Date
    Feb 2015
    Posts
    145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help update this Google search code

    Psuedocode would be something like
    Code:
    dim counter as long
    
    for counter = startingrow to getLastRow
       'do what you need to for each search
    next counter

    Then a working function for lastrow:
    Code:
    Function getLastRow(Optional columnNumber As Long = 1, _
                        Optional wksht As Worksheet) As Long
        If wksht Is Nothing Then Set wksht = ActiveSheet
        getLastRow = wksht.Cells(wksht.Rows.Count, columnNumber).End(xlUp).Row
    End Function

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •