I have a long list of names (about 8000) in an excel file that I need to find a linkedIn page for.

At the moment, I’m trying to use a macro in excel that extracts the first href link from the google homepage HTML code via the html library in VBA. I feel as if all my code is structured correctly but for some reason I’m still getting a runtime error.

Sub LinkedInLinks()

Dim URL As String
Dim lastRow As Long
Dim XMLHTTP As Object
Dim html As Object
Dim link As Object
Dim start_time As Date
Dim end_time As Date

lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim cookie As String
Dim result_cookie As String

start_time = Time
Debug.Print "start_time:" & start_time

For i = 2 To lastRow

    URL = Cells(i, 3)
        
       
    Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
    XMLHTTP.Open "GET", URL, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.send
        
        Set html = CreateObject("htmlfile")
            
    html.body.innerHTML = StrConv(XMLHTTP.responseBody, vbUnicode)
    Set link = html.getElementById("rso").getElementsByTagName("H3")(0).getElementsByTagName("a")(0)
        
    Cells(i, 5) = link.href
    DoEvents
Next

End Sub

The runtime error is occuring on the Set link line in the code and is saying that an object is required. I’m relatively new to VBA but a couple of things I did try were to define link as a variant as well as creating partition variables for every time I narrowed my search down via TagName or Id in which case that runtime error starts on whichever line comes first in this case.

I’ve looked across several forums and can’t seem to find a fix for my particular issue, so any input helps.