Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run-Time Error '91'

0

Hello, 

I just finished watching your tutorial, "Import web data to Excel using VBA Macros". I am receiving a run-time error '91' : Object variable or With block variable not set. Below is the code I am using. Please help!

Sub Get_web_Data()

Dim Request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant

' Website to go to.
website = "http://www.freddiemac.com/pmms/"

'Create the object that will make the webpage request.
Set Request = CreateObject("MSXML2.XMLHTTP")

'Where to go and how to go there - propobly don't need to change this.
Request.Open "GET", website, False

'Get fresh data.
Request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"

'Send the request for the webpage.
Request.send

'Get the webpage response data into a variable.
response = StrConv(Request.responseBody, vbUnicode)

' Put the webpage into an html object to make data references easier.
html.body.innerHTML = response

' Get the price from the specified element on the page.
PMMS = html.getElementsByClassName("mortgage-rate-widget__rate-value")(0).innerText

'Output the PMMS rate into a message box.
MsgBox PMMS_Rate

End Sub

Thank you in advance!!

excelmacros-vba
MS Office Version: Office 16
posted by: bconnor on Thu Sep 19, 2019 at 4:30 pm
Answer
Discuss

Answers

0

You probably get the error in this line of code.

PMMS = html.getElementsByClassName("mortgage-rate-widget__rate-value")(0).innerText

This line presumes that the object html was set. That was supposed to have happened in this line but perhaps it didn't.

html.body.innerHTML = response

It may not have done so if the site was not responding, even if it was slow to respond. But even if it did, the code getElementsByClassName("mortgage-rate-widget__rate-value")  does create another object, but only if the described element is found. If it isn't found it will not be created and therefore result in the error you complain about.

You can test for the existence of the html object by inserting

Debug.Print html Is Nothing

before the offending line. It will print True or False to the Immediate Window.

Add the following code in the same position.

Dim MyClass As Object
Set MyClass = getElementsByClassName("mortgage-rate-widget__rate-value") 
Debug.Print MyClass Is Nothing

Once you know which object is missing you can hunt for the error that caused it to be so.

posted by: Variatus (rep: 4889) on Thu Sep 19, 2019 at 6:07 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login