error running parsehtml()



  • I try to run the following macro,(and got run-time error 438 "object doesn't support this property or method" on line

    Set topics = html.getElementsByClassName("athing")

    [code]
    Public Sub parsehtml()
    Dim http As Object, html As New HTMLDocument, topics As Object, titleElem As Object, detailsElem As Object, topic As HTMLHtmlElement
    Dim i As Integer
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://news.ycombinator.com/", False
    http.send
    html.body.innerHTML = http.responseText
    Set topics = html.getElementsByClassName("athing")
    i = 2
    For Each topic In topics
    Set titleElem = topic.getElementsByTagName("td")(2)
    Sheets(1).Cells(i, 1).Value = titleElem.getElementsByTagName("a")(0).innerText
    Sheets(1).Cells(i, 2).Value = titleElem.getElementsByTagName("a")(0).href
    Set detailsElem = topic.NextSibling.getElementsByTagName("td")(1)
    Sheets(1).Cells(i, 3).Value = detailsElem.getElementsByTagName("span")(0).innerText
    Sheets(1).Cells(i, 4).Value = detailsElem.getElementsByTagName("a")(0).innerText
    i = i + 1
    Next
    End Sub

    Additional information for reference VBA project (see attachment)

    ![0_1494![0_1494674418041_Reference_VBA_project.JPG](/uploads/files/1494674422512-

    I am using excel 2013 during this test.
    any idea how to get this run?

    Regards,
    Benny
    reference_vba_project.jpg) 674340353_Reference_VBA_project.JPG](Uploading 100%)



  • Didn't find any issues running your code. Sometimes "Createobject" method doesn't feel comfortable with class elements. Anyways, give this a try. Same code, though!

    Sub ParseHtml()
    Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument
    Dim topics As Object, titleElem As Object
    Dim detailsElem As Object, topic As Object
    Dim i As Integer
    
    With http
        .Open "GET", "https://news.ycombinator.com/", False
        .send
        html.body.innerHTML = http.responseText
    End With
    
    Set topics = html.getElementsByClassName("athing")
    
        For Each topic In topics
        i = i + 1
        Set titleElem = topic.getElementsByTagName("td")(2)
            Cells(i, 1).Value = titleElem.getElementsByTagName("a")(0).innerText
            Cells(i, 2).Value = titleElem.getElementsByTagName("a")(0).href
        Set detailsElem = topic.NextSibling.getElementsByTagName("td")(1)
            Cells(i, 3).Value = detailsElem.getElementsByTagName("span")(0).innerText
            Cells(i, 4).Value = detailsElem.getElementsByTagName("a")(0).innerText
        Next topic
    End Sub


  • @shahin2137
    still get the same run-time error
    Set topics = html.getElementsByClassName("athing")



  • Have you debugged your code to see where it stops first? F8 key is for debugging, by the way.



  • it stop on

    Set topics = html.getElementsByClassName("athing")



  • Hi there! I don't really understand that the code which works at my place is not working in your end. Anyways, give this a shot.

    Sub HackerNews()
    Dim html As New HTMLDocument
    Dim topics As Object, post As HTMLHtmlElement
    
    With CreateObject("MSXML2.serverXMLHTTP")
        .Open "GET", "https://news.ycombinator.com/", False
        .send
        html.body.innerHTML = .responseText
    End With
    Set topics = html.getElementsByClassName("athing")
        For Each post In topics
            x = x + 1
            Cells(x, 1) = post.getElementsByClassName("storylink")(0).innerText
            Cells(x, 2) = post.getElementsByClassName("sitestr")(0).innerText
            Cells(x, 3) = post.NextSibling.getElementsByTagName("span")(0).innerText
            Cells(x, 4) = post.NextSibling.getElementsByTagName("a")(0).innerText
        Next post
        Set html = Nothing:  Set topics = Nothing
    End Sub


  • @shahin2137
    Thank you for your code.
    I try it and still the same error.
    Let us trace back from the references VBA project.
    Now My references VBA project was on:
    1.Visual Basic for applications
    2.Microsoft Excel 15.0 Object library
    3.OLE automation
    4.Microsoft HTML object library
    is that enough by using those 4 libraries to get your code running well?
    (I use microsoft excel 2013 for this test)

    Regards,
    Benny



    1. Microsoft html object library
    2. Microsoft xml, v6.0

    are the only and most important part to be referred. The first three libraries you talked about get attached automatically, you don't need to take care about that. So, open a new workbook, save it as .xlsm extension. Again, open the file with .xlsm extension if already closed, click developer, visual basic, tools and reference sequentially then check the two libraries i mentioned above. Now , paste the code in a module and run it. These are the very basic part so I thought you already did it. One more library you can add- MS Office 15 object library. Not compulsory, though. Thanks. Btw, I use MS Office 2013 as well.



  • I already follow your notes, and still got the same outcome..

    error on this part
    Set topics = html.getElementsByClassName("athing")

    let say that your file is working fine on your pc can I ask you to send me your working file so I can download it and running on my pc?

    Regards,
    Benny



  • Now, I'm gonna attach an excel file with this macro. Let me know what happens. Follow the link "https://www.dropbox.com/s/09eatfkl32xxnmq/HackerNews.xlsm?dl=0" and download the excel file.



  • @shahin2137
    I already download your file and try to run on 2 separate laptop just to make sure if something goes wrong with my main laptop.
    And the answer is your code was running on my second laptop but still keep failing on my main laptop..reinstall excel is the better solution or what do you suggest for this case?

    Regards,
    Benny



  • I don't have much knowledge on this bro. I suppose you should google the problem. Perhaps, i noticed somewhere that the error you encounter is a bug.



  • shahin2137,
    Thank you for your help so far, see again next time bro.

    Regards,
    Benny


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.