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

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,066
Office Version
  1. 2016
Platform
  1. Windows
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"

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

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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...

Code:
ActiveSheet.Cells(i, 2).Value = texttext.getAttribute("class")
 
Last edited:
Upvote 0
Hi Domenic

I change the line but I I'm now getting a Run time error "1004" Application defined or object defined error
 
Upvote 0
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...

Code:
    [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?
 
Upvote 0
It does and does not help

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

Code:
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>
[TD="width: 79"][/TD]

</tbody>
 
Upvote 0
To list the urls, try...

Code:
    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...

Code:
    Dim div As Object
    Dim link As Object

Hope this helps!
 
Upvote 0
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?
 
Last edited:
Upvote 0
To go to the next page...

Code:
internetdata.getElementById("pnnext").Click

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

Code:
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...

Code:
    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!
 
Upvote 0
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.

Code:
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
Code:
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.



Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

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
Back
Top