In this article, we will look at how to extract data from a website using VBA. For this we will be using the navigate method on the InternetExplorer object. Let us see this in detail.

Example 1:

Say, you want to pull the current temperature of the city of Vancouver from this site. Before we begin our coding, we need to know the tag element that contains the required data in the web site.

To do that, navigate to the site in the browser. Right click on the data to be retrieved and click on inspect element.

The element will be highlighted in the Developer Tools window that will be opened.

So, we basically want to extract the inner text of a “p” tag with class “temperature”. This can be done as follows.

First of all, we need to make sure that the required libraries are included in your VBA project, namely, Microsoft HTML Object Library and Microsoft Internet Controls.

Now, let’s start with the code. Create a new InternetExplorer object using

Dim IE As New InternetExplorer

Specify the URL from which to extract data and navigate to it using

url = "https://www.theweathernetwork.com/ca/weather/british-columbia/vancouver"

IE.navigate url

For troubleshooting purpose, you can make the browser window visible by using:

 IE.Visible = True

After navigating to a URL, you need to wait till the web page is entirely loaded. This can be done by using

 Do
 DoEvents
 Loop Until IE.readyState = READYSTATE_COMPLETE

(After the webpage is completely loaded, the readyState property of the InternetExplorer object is set to READYSTATE_COMPLETE)

Next, you save the contents of the webpage loaded using the document property

  Dim Doc As HTMLDocument

Set Doc = IE.document

You can then extract all elements of a particular type from the HTML document using

 Dim tagElements As Object

 Set tagElements = Doc.all.tags("p")

To loop through all these “p” tag elements

 Dim element As Object

For Each element In tagElements

'Code to pull temperature goes here

Next

From all the p tags, we are looking for the tag that contains temperature (°C) and has class name “temperature”. For this we use the element.innerText and element.className properties as follows

 If InStr(element.innerText, "°C") And InStr(element.className, "temperature") Then
 Sheet1.Cells(1, 1).Value = element.innerText
 End If

Finally, we do the cleanup using

 IE.Quit
 Set IE = Nothing

Here is the entire thing put together

Sub extractVancouverData()

Dim IE As New InternetExplorer
 Dim url As String
 Dim item As HTMLHtmlElement
 Dim Doc As HTMLDocument
 Dim tagElements As Object
 Dim element As Object
 Dim lastRow

 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 Application.EnableEvents = False
 Application.Calculation = xlCalculationManual

 url = "https://www.theweathernetwork.com/ca/weather/british-columbia/vancouver"

IE.navigate url

 IE.Visible = True

 Do
 DoEvents
 Loop Until IE.readyState = READYSTATE_COMPLETE

Set Doc = IE.document

lastRow = Sheet1.UsedRange.Rows.Count + 1

 Set tagElements = Doc.all.tags("p")
 For Each element In tagElements

  If InStr(element.innerText, "°C") > 0 And InStr(element.className, "temperature") > 0 Then
    Sheet1.Cells(lastRow, 1).Value = element.innerText
    ' Exit the for loop once you get the temperature to avoid unnecessary processing
    Exit For
  End If
 Next

IE.Quit
 Set IE = Nothing

 Application.ScreenUpdating = True
 Application.DisplayAlerts = True
 Application.EnableEvents = True
 Application.Calculation = xlCalculationAutomatic

End Sub

You can also extract all elements from the HTMLDocument by the element’s ID, className, Name or tagName using the respective method as shown below

Set tagElements = Doc.getElementsByClassName("temperature")

For Each element In tagElements

If InStr(element.innerText, "°C") Then
Sheet1.Cells(lastRow, 1).Value = element.innerText
End If

Next

Example 2:

Many times while extracting data from a website, you need to go to a page and click on a link as you cannot directly access the results using a URL. For this you can use the element.Click method. Say you click on the link “Excel” on the home page of this site. This is how you can do it

Sub clickOnLink()

Dim IE As New InternetExplorer
Dim str As String
Dim Doc As HTMLDocument
Dim tagElements As Object
Dim element As HTMLObjectElement

str = "https://software-solutions-online.com/"

IE.navigate str
IE.Visible = True

Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Set Doc = IE.document

'First get all the links

Set tagElements = Doc.all.tags("a")

' Loop through all the links
For Each element In tagElements

' Look for the link that contains the text Excel
If element.innerText = "Excel" Then

'Click on the link
element.Click

Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Exit For
End If
Next

If you run this code with the IE visible, you will see that the code clicks on the Excel link. Now you can extract the required data using the methods described above.

If you’re interested in doing something similar via recording a macro, please have a look at this article and for using a query table check out this one. And also check here for the documentation on the Instr function.

Finally, you can get more info on Application.Calculation here.