Issue with a line of code - "Object doesn't support this property or method"

[Sharid]

Hi

Could somebody please help me,

The following code is should navigate to google and then pull all the urls from the link to a sheet. I keep having issues with the line of code shown in red. "Object doesn't support this property or method"

Sub webpage()

    Dim internet As Object
    Dim internetdata As Object
    Dim div_result As Object
    Dim header_links As Object
    Dim link As Object
    Dim URL As String

    Set internet = CreateObject("InternetExplorer.Application")
    internet.Visible = True

    URL = "https://www.google.co.in/search?q[COLOR=#000000]=how+to+program+in+vba"[/COLOR]
    internet.Navigate URL

    Do Until internet.ReadyState >= 4
        DoEvents
    Loop

    Application.Wait Now + TimeSerial(0, 0, 5)

    Set internetdata = internet.Document
    Set div_result = internetdata.getElementById("res")


    Set header_links = div_result.getElementsByTagName("h3")

    For Each h In header_links
        Set link = h.ChildNodes.Item(0)
        'Cells(Range("A" & Rows.Count).End(xlUp).Row + 1, 1) = link.href
        Set internetdata = internet.Document
        Set internetdatalink = internetdata.getElementById("rso")
        Set internetlink = internetdatalink.getElementsByTagName("h3")
        For Each texttext In internetlink
       [COLOR=#ff0000] ActiveSheet.Cells(i, 2).Value = texttext.href[/COLOR]
        i = i + 1
        Next texttext
    Next

    MsgBox "done"
End Sub

Thanks for having a look

 

[Domenic]

To get an attribute for an element, you'll need to use the getAttribute method. However, if you inspect the element, you'll see that it doesn't contain an href attribute, but it does have a class attribute. So, as an example, here's how you would get the class attribute...

ActiveSheet.Cells(i, 2).Value = texttext.getAttribute("class")

 

[Sharid]

Hi Domenic

I change the line but I I'm now getting a Run time error "1004" Application defined or object defined error

 

[Domenic]

It looks like the problem lies with your variable i, since it has not been declared nor has it been initialized. As a result, i is assigned Empty, hence the error. Try initializing your variable before the For Each/Next loop...

    [COLOR=#ff0000]i = 1 [/COLOR][COLOR=#008000]'start at Row 1[/COLOR][COLOR=#ff0000]
[/COLOR]    For Each h In header_links
        Set link = h.ChildNodes.Item(0)
        'Cells(Range("A" & Rows.Count).End(xlUp).Row + 1, 1) = link.href
        Set internetdata = internet.Document
        Set internetdatalink = internetdata.getElementById("rso")
        Set internetlink = internetdatalink.getElementsByTagName("h3")
        For Each texttext In internetlink
        ActiveSheet.Cells(i, 2).Value = texttext.getattribute("class")
        i = i + 1
        Next texttext
    Next

Does this help?

 

[Sharid]

It does and does not help

It pulls up the class "LC20lb" but NOT the urls, this is the code now

Sub webpage()

    Dim internet As Object
    Dim internetdata As Object
    Dim div_result As Object
    Dim header_links As Object
    Dim link As Object
    Dim URL As String

    Set internet = CreateObject("InternetExplorer.Application")
    internet.Visible = True

    URL = "https://www.google.co.uk/search?q=how+to+program+in+vba"
    internet.Navigate URL

    Do Until internet.ReadyState >= 4
        DoEvents
    Loop

    Application.Wait Now + TimeSerial(0, 0, 5)
    Set internetdata = internet.Document
    Set div_result = internetdata.getElementById("res")
    Set header_links = div_result.getElementsByTagName("h3")


[COLOR=#ff0000] i = 1 [/COLOR]'start at Row 1
For Each h In header_links
        Set link = h.ChildNodes.Item(0)
        Set internetdata = internet.Document
        Set internetdatalink = internetdata.getElementById("rso")
        Set internetlink = internetdatalink.getElementsByTagName("h3")
        For Each texttext In internetlink
       [COLOR=#ff0000] ActiveSheet.Cells(i, 2).Value = texttext.getAttribute("Class")[/COLOR]
        
        i = i + 1
        Next texttext
    Next

    MsgBox "done"
End Sub

<colgroup><col width="79"></colgroup><tbody>
</tbody>

 

[Domenic]

To list the urls, try...

    i = 2
    For Each div In internetdata.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

And, of course, you can declare div and link as an Object type...

    Dim div As Object
    Dim link As Object

Hope this helps!

 

[Sharid]

Thanks this is a lot better, it has pulled up a few duplicate urls AS SHOWN BELOW. I will need to look into this


https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/
https://www.excel-easy.com/vba.html
--https://powerspreadsheets.com/excel-macro-tutorial-for-beginners/
--https://support.office.com/en-ie/article/find-help-on-using-the-visual-basic-editor-61404b99-84af-4aa3-b1ca-465bc4f45432
https://www.excel-easy.com/vba.html
--https://www.guru99.com/creating-your-first-visual-basic-for-applications-vba-in-excel.html
--https://www.makeuseof.com/tag/vba-macros-excel-tutorial/
--https://www.homeandlearn.org/
--https://www.educba.com/vba-excel-programming/
--https://www.i-programmer.info/ebooks/automating-excel/1264-getting-started.html
--https://www.excel-vba.com/excel-vba-solutions-beginners.htm
--https://www.tutorialspoint.com/vba/html
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

It only pulls data from the first page, is there a way to set it to pull an X amunt of URLs or work through a pantigation?

 

[Domenic]

To go to the next page...

internetdata.getElementById("pnnext").Click

Once it's at the next page, you'll need to assign the new document to internetdata...

Set internetdata = internet.document

So, let's assume that you want to scrape the data from the first 3 pages, you would do something like this...

    pageNumber = 1
    i = 2
    Do
        For Each div In internetdata.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 >= 3 Then Exit Do 'the first 3 pages
        internetdata.getElementById("pnnext").Click 'next web page
        Do While internet.Busy Or internet.readyState <> 4
            DoEvents
        Loop
        Set internetdata = internet.document
        pageNumber = pageNumber + 1
    Loop

Hope this helps!

 

[Sharid]

Hi Domenic

Your code is super, way better than what I was doing. The full code is below incase anybody else needs it,

Just one more thing, as you know this bit of the code tells google where to navigate too. The bit in RED is my search item area.

URL = "https://www.google.co.uk/search?q=[COLOR=#ff0000]how[/COLOR][B][COLOR=#0000ff]+[/COLOR][/B][COLOR=#ff0000]to[/COLOR][B][COLOR=#0000ff]+[/COLOR][/B][COLOR=#ff0000]program[/COLOR][B][COLOR=#0000ff]+[/COLOR][/B][COLOR=#ff0000]in[/COLOR][B][COLOR=#0000ff]+[/COLOR][/B][COLOR=#ff0000]vba[/COLOR]"

Is there a way to run it from a cell so whatever I put in the cell, that is what replaces what is in red, this way I do not need to keep changing the code for every new item to search.

Some thing like this

URL = "https://www.google.co.uk/search?q=[COLOR=#ff0000]Sheet1.cell A1= value[/COLOR]"

So whatever I put in to sheet1 cell A1 that will apear in the vba code. Any blank spaces will need to have a plus (+) simple in them as show above in Blue.

Sub webpage()

    Dim internet As Object
    Dim internetdata As Object
    Dim div_result As Object
    Dim header_links As Object
    Dim link As Object
    Dim URL As String
    Dim ie As InternetExplorer
    Set ie = New InternetExplorer

    Set internet = CreateObject("InternetExplorer.Application")
    internet.Visible = True

    URL = "https://www.google.co.uk/search?q=how+to+program+in+vba"
    internet.Navigate URL

    Do Until internet.readyState >= 4
        DoEvents
    Loop

    Application.Wait Now + TimeSerial(0, 0, 5)
    Set internetdata = internet.document
    Set div_result = internetdata.getElementById("res")
    Set header_links = div_result.getElementsByTagName("h3")


pageNumber = 1
    i = 2
    Do
        For Each div In internetdata.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 >= 6 Then Exit Do 'the first 6 pages
        internetdata.getElementById("pnnext").Click 'next web page
        Do While internet.Busy Or internet.readyState <> 4
            DoEvents
        Loop
        Set internetdata = internet.document
        pageNumber = pageNumber + 1
    Loop

    MsgBox "done"
     

End Sub

 

[Sharid]

I looking for something like this, but I can't get this to work

https://www.automateexcel.com/vba/search-google-in-excel-cell/