Run-time error '91' (Object variable or With block variable not set)
I am relatively new to VBA and am trying to put together a msgbox that will give me a specific number from a web scrape, however I keep running into a run-time error '91' and I simply cannot figure out how to fix this. I have searched countless stackoverflow questions, youtube videos and generic google searches, however have not been successful in finding out the error on my own.
Here is the code:
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate ("http://brokercheck.finra.org")
Do
DoEvents
Loop Until IE.ReadyState = 4
'Enter values from the corresponding sheet
'Set some generic typing for ease
Set doc = IE.document
doc.GetElementbyID("GenericSearch_IndividualSearchText").Value = Worksheets("Master").Range("D203")
doc.GetElementbyID("GenericSearch_EmploymingFirmSearchText").Value = Worksheets("Master").Range("C203")
Set elements = doc.getElementsByTagName("button")
For Each element In elements
If element.getAttribute("type") = "submit" Then
element.Click
Exit For
End If
Next element
Do
DoEvents
Loop Until IE.ReadyState = 4
'find CRD#
Set crd = doc.getElementsByClassName("summarydisplaycrd")(0).innerText 'here is where the run time error occurs
MsgBox crd
and the HTML I am trying to get the information from:
<div class="searchresulttext">
<div class="bcrow">
<div class=""> <span class="summarydisplayname">[redacted]</span> <span class="summarydisplaycrd text-nowrap">(CRD# 5944070)</span></div>
I'm reviewing this code and the finra.org site, and have the following observations, which when addressed, should resolve the problem.
-
The HTML example you provided is simply incorrect, based on the actual HTML that is returned from the "Check" button.
The actual HTML returned looks like this, and the classname is
"displayname"
, not"summarydisplaycrd"
:
<div class="SearchResultItemColor bcrow">
<div class="searchresulttext">
<div class="bcsearchresultfirstcol">
<span class="displayname">[redacted]</span> <span class="displaycrd">(CRD# 123456789)</span>
- Your code exits the
For each element
loop upon finding the first "submit" button. This may not be the "Check" button (although I can get results either way, you may want to add more logic in the code to ensure the "Check " button is submit.
UPDATE
On further review, while I can replicate the Type 91 error, I still don't know why your class name appears different than mine (maybe an IE11 thing, dunno...) in any case, I'm able to resolve that by forcing a longer delay, as in this case the DoEvents
loop is simply not adequate (sometimes this is the case when data is served dynamically from external functions, the browser is ReadyState=4 and .Busy=True, so the loop doesn't do anything)
I use the WinAPI Sleep function and force a 1 second delay after the "Click" button pressed, looping on condition of ReadyState = 4
and .Busy=True
.
NOTE you will need to modify the classname parameter depending on how it is appearing on your HTML.
Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub finra()
Dim IE As Object
Dim doc As Object, element As Object, elements As Object, crd
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate ("http://brokercheck.finra.org")
Call WaitIE(IE, 1000)
'Enter values from the corresponding sheet
'Set some generic typing for ease
Set doc = IE.document
doc.GetElementbyID("GenericSearch_IndividualSearchText").Value = "steve"
doc.GetElementbyID("GenericSearch_EmploymingFirmSearchText").Value = "ed"
Set elements = doc.getElementsByTagName("button")
For Each element In elements
If element.getAttribute("type") = "submit" Then
If element.innerText = "Check " Then
element.Click
Exit For
End If
End If
Next element
Call WaitIE(IE, 1000)
Dim itms As Object
'Set itms = doc.getElementsByClassName("displaycrd")
crd = doc.getElementsByClassName("displaycrd")(0).innerText 'here is where the run time error occurs
MsgBox crd
End Sub
Sub WaitIE(IE As Object, Optional time As Long = 250)
Dim i As Long
Do
Sleep time
Debug.Print CStr(i) & vbTab & "Ready: " & CStr(IE.ReadyState = 4) & _
vbCrLf & vbTab & "Busy: " & CStr(IE.Busy)
i = i + 1
Loop Until IE.ReadyState = 4 And Not IE.Busy
End Sub