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"