1

Constantly getting 

Run-time error '-2147417848 (80010108)':

Automation error

The object invoked has disconnected from its clients

I'm messing around with HTML and trying to learn how to pull values from websites. For this test I'm opening my Stack Overflow profile and pulling my rep value to place in a cell. I have all my reference libraries enabled, but I continuously get this error.

Option Explicit
Sub GetTheValue()
    Dim IE As InternetExplorer, retrievedvalue As Variant, oHTML_Element As IHTMLElement

    Set IE = New InternetExplorerMedium
    IE.Visible = True

    IE.navigate "https://stackoverflow.com/users/7668613/dwirony"

    Application.Wait (Now + TimeValue("0:00:05"))

    For Each oHTML_Element In IE.Document.getelementsbyID("top-cards")
        If oHTML_Element.classname = "g-col fl-none -rep" Then
            retrievedvalue = oHTML_Element.InnerText
            Exit For
        End If
    Next oHTML_Element

    Workbooks("Book1").Worksheets("Sheet1").Range("A1").Value = retrievedvalue
End Sub

Error occurs on line 

For Each oHTML_Element In IE.Document.getelementsbyID("top-cards")

Here's the snippet I'm trying to read from:

<div id="top-cards" class="g-row _gutters p-highlights">

<aside class="g-col g-column -card -reputation js-highlight-box-reputation">
    <h1 class="g-col -title">Reputation</h1>
    <div class="g-row -row-first">
        <div class="g-col g-column">
            <div class="g-row _gutters fl-none">
                <span class="g-col fl-none -rep">897</span>
Community
  • 1
  • 1
dwirony
  • 5,487
  • 3
  • 21
  • 43
  • `getelementsbyID` should be `getelementbyID` and returns a single element, not a collection. ID should be unique in a document. – Tim Williams Sep 25 '17 at 17:13
  • @TimWilliams is absolutely correct, also random question, is your computer's region set to non-US? – chiliNUT Sep 25 '17 at 17:23
  • @chiliNUT It's set to US, why? – dwirony Sep 25 '17 at 17:23
  • @Tim Williams I'm fairly new to HTML - when you say that it returns a single element and not a collection, are you saying that there's no need to loop through? – dwirony Sep 25 '17 at 17:24
  • @TimWilliams Obviously `getelementsbyID` is wrong, but the error will appear even if referencing to `IE.Document` and caused by IE ActiveX disconnection as OP shown. @dwirony Try to play with variations of IE [compatibility](https://www.google.ru/search?q=ie+compatibility+mode&tbm=isch), [browser](https://www.google.ru/search?q=ie+browser+mode&tbm=isch) and [document](https://www.google.ru/search?q=ie+document+mode&tbm=isch) modes. Take a look at [this](https://stackoverflow.com/a/41595471/2165759). – omegastripes Sep 25 '17 at 18:33
  • @omegastripes I think you're right, I need to mess with my settings. After add stackoverflow.com to the compatibility list my error is now `Automation error: The interface is unknown`. – dwirony Sep 25 '17 at 18:54
  • @omegastripes Ahhh I got it! I had to uncheck `Enable protected mode` in my security settings in order for it to pull through. Thanks for the help – dwirony Sep 25 '17 at 18:58
  • @dwirony I would use XHR to avoid IE at all. – omegastripes Sep 25 '17 at 19:09

2 Answers2

1

Here's two methods:

Set doc = IE.document

'1. Drill down level by level
Set el = doc.getElementById("top-cards")
Debug.Print el.getElementsByTagName("div")(0). _
               getElementsByTagName("div")(0). _
               getElementsByTagName("span")(0).innerText

'2. Use a query selector
Debug.Print doc.querySelector("#top-cards div div span").innerText
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Trying your first solution still returns the same Run-time error on the line `Set doc = IE.Document`. – dwirony Sep 25 '17 at 17:55
  • Works for me (note URL is not as in your question though; `https://stackoverflow.com/users/7668613/dwirony?tab=topactivity`) – Tim Williams Sep 25 '17 at 18:00
  • Hm, I think omegastripes is right in saying there's something wrong with my ActiveX connections. I'm messing around with it a bit and I've gotten the error message to change to `Automation error: The interface is unknown.` I'm gonna continue to mess around with the settings a bit. – dwirony Sep 25 '17 at 18:53
  • I got it figured out - I had to uncheck "Enabled protected mode" in my security settings. It's pulling through perfectly now. Thanks – dwirony Sep 25 '17 at 18:59
1

Try to scrape with XHR:

Sub Test()

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://stackoverflow.com/users/7668613/dwirony", False
        .Send
        Debug.Print CLng(Split(Split(.ResponseText, """reputation"">", 2)(1), "<", 2)(0))
    End With

End Sub
omegastripes
  • 12,351
  • 4
  • 45
  • 96
  • I tried this code and got a type mismatch, but this is really interesting! I'm definitely going to look in this. – dwirony Sep 25 '17 at 20:30