CODEWITHSUNDEEP
htmlexcelvbaweb-scrapingruntime-error
Reason
Reason

Reputation: 15

Web scraping: Run-time error 91 Object variable not set

I am trying to update my species database with information from this website: https://www.afcd.gov.hk/english/conservation/hkbiodiversity/database/search.php

I have an xlsm with the list of species in column A, and search each of them in the search engine of the website, which leads to a page showing a link to another page dedicated to that particular species. Each species is identified by a unique ID, which is the information I want.

e.g. If I input "Mnais mneme" in the search box "Scientific Name", a page showing a table containing that species with a link (https://www.afcd.gov.hk/english/conservation/hkbiodiversity/database/popup_record.php?id=781&lang=en) attached to its name will appear. "781" would be the species ID.

I want to copy this link into column B of my xlsm and extract the ID in Excel:

Sub SearchBot()
 
    'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser
    
    'link will be the <a> carrying the href with the species id
    Dim link As HTMLAnchorElement
              
    'define y as interger counter
    Dim y As Integer
               
    'initiating a new instance of Internet Explorer and assigning it to objIE
    Set objIE = New InternetExplorer
 
    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True
 
    'navigate IE to this web page
    objIE.navigate "https://www.afcd.gov.hk/english/conservation/hkbiodiversity/database/search.php"
 
    'wait here for a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    
    objIE.document.getElementById("s1").Click
 
    'in the search box put cell "A2" value
    objIE.document.all.Item("scientific_name").Value = Sheets("Sheet1").Range("A2").Value
 
    'click the 'Search' button
    objIE.document.getElementsByClassName("btn_3")(1).Click
    
    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    
    'select the species name link
    Set link = objIE.document.getElementsByTagName("td")(4).getElementsByTagName ("a")(0)
                
    y = 2
        
    'print the link to column B in Sheet1
    Sheets("Sheet1").Range("B" & y).Value = link.href
    
End Sub

Debugging shows

run-time error 91

when stopping at the last line:

Sheets("Sheet1").Range("B" & y).Value = link.href

Is it a problem with setting link as HTMLAnchorElement? I tried setting it as Object instead but the error still comes up.

Upvotes: 0

Views: 305

Answers (1)

Ryan Wildry
Ryan Wildry

Reputation: 5677

Here's some code that uses a web request to find the data you are interested in. I looked at the page, and it appears the check box contains the SpeciesID too, so, I used the name of that control to find the input's value.

Code

Public Function GetSpeciesID(ScientificName As String) As String
    Dim requestURL      As String
    Const InvalidValue  As String = "-1"
    
    'If the Scientific Name is blank, return a default value
    If (Trim$(ScientificName) = vbNullString) Then
        GetSpeciesID = InvalidValue
        Exit Function
    End If
    
    requestURL = "https://www.afcd.gov.hk/english/conservation/hkbiodiversity/database/doSearch.php?" & _
    "entity_id=0&family_name=&scientific_name=" & WorksheetFunction.EncodeURL(ScientificName) & _
    "&common_name=&chinese_name=&hk_protection_status_val=&chinared_status_val=&iucn_status_val="
    
    With CreateObject("MSXML2.ServerXMLHTTP.6.0")
        .Open "GET", requestURL
        .send
        
        Dim html As Object: Set html = CreateObject("htmlfile")
        html.body.innerhtml = .responseText
    End With
    
    GetSpeciesID = html.getElementsByName("check")(0).Value
End Function

'Run this method
Public Sub Runner()
    Debug.Print GetSpeciesID("Mnais mneme")
End Sub

Upvotes: 1

Related Questions