Scraping information from a website using VBA

Question

I'm trying to scrape information about the aggregated 30-year fixed mortgage rate for the US using VBA in Excel. Here's what I have so far, but I can't figure out what I'm doing wrong:

Sub National_Average()
'
' National_Average Macro
' Record the national average mortgage rate from https://fred.stlouisfed.org/series/MORTGAGE30US

Dim ie As InternetExplorer
Dim htmlEle As IHTMLElement
Dim i As Integer

i = 1

Set ie = New InternetExplorer

ie.Visible = True

ie.navigate "https://fred.stlouisfed.org/series/MORTGAGE30US"

Application.Wait (Now + TimeValue("00:00:05"))

For Each htmlEle In ie.document.getElementsByClassName("series-meta-observation-value")

With Sheets("Demand")

.Range("F2").Value = htmlEle.getElementsByClassName("series-meta-observation-value").textContent

End With

Next

    ie.Quit
End Sub

I know it's got something to do with my For Each section, specifically the Range("F2").Value portion. Help would be appreciated!

You are not incrementing .Range("F2").Value if you are expecting more. So you will need to add a counter and use .Range("F2").offset(lngCounter,0).value I think it would just be htmlEle.textContent — Nathan_Sav
– Nathan_Sav, Commented Mar 27, 2020 at 17:44
I tried that, but I got an error saying "Object variable or with block variable not set". Suggestions? How do I add a counter? I'm really new at this VBA coding. — CRE123
– CRE123, Commented Mar 27, 2020 at 17:48
Are you expecting a single value? There may be a better/faster way. — QHarr
– QHarr, Commented Mar 27, 2020 at 20:10

HeeLee · Accepted Answer · 2020-03-27 17:54:39Z

1

You are looping on each element found by class already, why are you using the .getelementsbyclass() again on the htmlEle to assign the value. it should be like below

For Each htmlEle In ie.document.getElementsByClassName("series-meta-observation-value")
With Sheets("Demand")
.Range("F2").Value = .Range("F2").Value & htmlEle.innertext 'accumulating all data on cell
End With
Next htmlEle

answered Mar 27, 2020 at 17:54

HeeLee

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Scraping information from a website using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related