Pulling Link Text from Web Page - Visual Basic Excel

Question

Quick Context: I have a basic excel Spreadsheet with three fields: Client_URL, Client_Name and AHREFs_Rank. When a URL is entered into Client_URL I want to:

Login to AHREFs;
Paste and enter the client URL into a web form field using the value of cell Client_URL;
Pull the AHREF's Rank number for the inner text of a link.

I'm having trouble with step 3.

Here's the full code for Step 3 used so far, I'm experiencing "Compile Error: Object Required" issues.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = Range("Client_URL").Row And _
       Target.Column = Range("Client_URL").Column Then

        Dim HTMLDoc As HTMLDocument
        Dim MyBrowser As InternetExplorer
        Dim MyHTML_Element As IHTMLElement
        Dim MyURL As String

        MyURL = "https://ahrefs.com/user/login"
        Set MyBrowser = New InternetExplorer
        MyBrowser.navigate MyURL
        MyBrowser.Visible = True

        Do
        Loop Until MyBrowser.readyState = READYSTATE_COMPLETE

        Set HTMLDoc = MyBrowser.document
        HTMLDoc.all.Email.Value = "login@email.com"
        HTMLDoc.all.Password.Value = "password123"

        For Each MyHTML_Element In HTMLDoc.getElementsByTagName("input")
            If MyHTML_Element.Type = "submit" Then MyHTML_Element.Click: Exit For
        Next

        MyURL = "https://ahrefs.com/dashboard/metrics"
        MyBrowser.navigate MyURL

        Do
        Loop Until MyBrowser.readyState = READYSTATE_COMPLETE

        Set HTMLDoc = MyBrowser.document
        HTMLDoc.activeElement.Value = Range("Client_URL").Value
        HTMLDoc.getElementById("dashboard_start_analysing").Click

        Do
            DoEvents
        Loop Until MyBrowser.readyState = READYSTATE_COMPLETE

        Set HTMLDoc = MyBrowser.document
        Dim rankInnertext As Object
        rankInnertext = HTMLDoc.getElementById("topAhrefsRank").innerText

        MsgBox rankInnertext

    End If

End Sub

If I remove the "Set" from "rankInnertext =" i then get a Runtime Error 91 "Object Variable Not Set".

To break it down, in Step 3 I've: Submitted a field on a previous page and am waiting for the current page to finish loading. I'm then attempting to pull the inner text of a link with the ID "topAhrefsRank" and set the value of the cell "AHREFs_Rank" to equal the value of the inner text as a string.

Very new to visual basic so any help is appreciated.

Update: Changed code as per suggestions. Have changed rankInnertext from String to Object. Now receiving "Run Time Error 91: Object Variable or With block variable not set"

Drop `set` keyword as `rankInnerText` is not an object and test. — shrivallabha.redij, Apr 21 '18 at 08:25
you may need to show a little more code. Can you share the URL? It maybe that HTMLDoc.getElementById("topAhrefsRank") is incorrect or not retrievable at the time it is called. — QHarr, Apr 21 '18 at 08:30
Apart from what shrivallabha.redij has already suggested, you can check like this as well to see what you get `Dim rankInnertext As Object : Set rankInnertext = HTMLDoc.getElementById("topAhrefsRank") : Msgbox rankInnertext.innerText`. Make sure to kick out `semicolon` and start `newline` from there instead. — SIM, Apr 21 '18 at 08:41
Hi @SIM I've updated the code exactly as you suggested but I am getting an error code on the last like "Msgbox rankInner.text.innerText" (Error 91 - Object Variable Not Set" — Neelhtak, Apr 21 '18 at 10:44
Hi @shrivallabha.redij as mentioned in original post, when dropping `set` the error changes from a Compile Error to an Object Variable Not Set error. — Neelhtak, Apr 21 '18 at 10:46

score 0 · Answer 1 · answered Apr 21 '18 at 16:34

0

Try this - do you get an error?

    Dim rankInnertext '<< variant OK here
    rankInnertext = HTMLDoc.getElementById("topAhrefsRank").innerText
    MsgBox rankInnertext

answered Apr 21 '18 at 16:34

Tim Williams

154,628
8
97
125

Yes - Error 91 "Object Variable Not Set". – Neelhtak Apr 22 '18 at 00:03
Seems there's no element with that id? – Tim Williams Apr 22 '18 at 00:07
Ah, but there is! I think the thing possibly preventing this is that the element may not be available on page load (it uses a data source/script to pull a number/rank) and loading this may be separate to page loading, so when the page is finished loading it tries to pull this element before its completely loaded. – Neelhtak Apr 22 '18 at 00:25

score 0 · Answer 2 · answered Apr 22 '18 at 00:58

UPDATE! I've managed to solve this issue by forcing VBA to wait a specific time in order to ensure all page scripts had loaded. For some reason no matter how I tried to wait until the page loaded, the elements were not available as they were loading through scripts.

I enforced a time delay as per the code below:

    Dim time1
    Dim time2

    time1 = Now
    time2 = Now + TimeValue("0:00:20")
     Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

The correct value is now being displayed in a msgbox, so I can now pull this data through and assign it as the value of an Excel cell, which is exactly what I'm after.

Thanks to everyone who responded - greatly appreciated!

That is typical AJAX webpage behaviour. Using timer for delay is not reliable. Try to make additional checks like [this](https://stackoverflow.com/a/32170074/2165759) and [this](https://stackoverflow.com/a/47084865/2165759). — omegastripes, Apr 22 '18 at 07:34

Pulling Link Text from Web Page - Visual Basic Excel

2 Answers2