title: Return URL From First Search Result
body: <p>I have an Excel workbook of around 25,000 company keywords from which I'd like to get the company website URL.</p>
  <p>I am looking to run a VBA script which can run these keywords as a Google search, and pull the URL of the first result into a spreadsheet.</p>
  <p>I found a <a href="https://stackoverflow.com/questions/17495644/using-vba-in-excel-to-google-search-in-ie-and-return-the-hyperlink-of-the-first/17499488">similar thread</a>.<br />
  The results of this to be hit-and-miss; some keywords return the URL in the next column, others remain blank.<br />
  It also seemed to pull the URL of Google's optimised sub-links in the first search result rather than the main website URL: <a href="https://i.stack.imgur.com/hlgmz.png" rel="nofollow noreferrer">Google Search Result example</a></p>
  <p>I then found the below code <a href="https://re-reality.com/technology/web-scrapping-google-search-result-through-vba/" rel="nofollow noreferrer">here</a> which I ran on a sample list of 1,000 keywords. The author of this blog stipulates that this code works for Mozilla Firefox.</p>
  <p>I tested IE code that he has also written but this did not achieve the same results (it was adding hyperlinks consisting of descriptive text from the search results rather than the raw URL).</p>
  <p>The Firefox code worked until the 714<sup>th</sup> row, then returned a error message</p>
  <blockquote>
  <p>&quot;<em><strong>Run time error 91: object variable or with block variable not set</strong></em>&quot;</p>
  </blockquote>
  <p>Spreadsheet layout showing successful results and row at which macro stopped<br />
  <a href="https://i.stack.imgur.com/48Hbk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/48Hbk.png" alt="enter image description here" /></a></p>
  <pre class="lang-vb prettyprint-override"><code>Sub GoogleURL ()
  
      Dim url As String, lastRow As Long
      Dim XMLHTTP As Object
      Dim html As Object
      Dim objResultDiv As Object
      Dim objH As Object
  
      lastRow = Range(“A” &amp; Rows.Count).End(xlUp).Row
  
      For i = 2 To lastRow
  
          url = “https://www.google.co.uk/search?q=” &amp; Cells(i, 1) &amp; “&amp;rnd=” &amp; WorksheetFunction.RandBetween(1, 10000)
  
          Set XMLHTTP = CreateObject(“MSXML2.serverXMLHTTP”)
  
          XMLHTTP.Open “GET”, url, False
  
          XMLHTTP.setRequestHeader “Content-Type”, “text/xml”
  
          XMLHTTP.setRequestHeader “User-Agent”, “Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0”
  
          XMLHTTP.send
  
          Set html = CreateObject(“htmlfile”)
  
          html.body.innerHTML = XMLHTTP.ResponseText
  
          Set objResultDiv = html.getelementbyid(“rso”)
  
          Set objH = objResultDiv.getelementsbytagname(“h3”)(0)
  
          Cells(i, 2).Value = objH.innerText
  
          Set html = CreateObject(“htmlfile”)
  
          html.body.innerHTML = XMLHTTP.ResponseText
  
          Set objResultDiv = html.getelementbyid(“rso”)
  
          Set objH = objResultDiv.getelementsbytagname(“cite”)(0)
  
          Cells(i, 3).Value = objH.innerText
  
          DoEvents
  
      Next
  
  End Sub
  </code></pre>