How to Use Web Scraping with Excel VBA (3 Suitable Examples)

Web Scraping is a process of extracting data from websites and converting it into a format that can be easily accessed and analyzed. Microsoft Excel is a popular tool for data analysis, and it can also be used for web scraping. In this article, I am going to explain how to use web scraping with Excel VBA with 3 suitable examples. I hope it will be helpful for you if you are looking for a similar sort of thing.

Download Practice Workbook

3 Suitable Examples to Use Web Scraping with Excel VBA

In order to scrap data with Excel VBA, we need to add some references. For this, Follow the following procedures.

Steps:

• Go to the Developer tab first.
• Next, click on Visual Basic from the ribbon.

• Now, choose the References… option from Tools.

• Check the Microsoft Internet Controls and Microsoft HTML Object Library references.

Thus, the necessary references will be added to do web scraping.

Example 1: Scraping a Table from Web with Excel VBA

In this method, we will use VBA code to scrape data from a website into Excel. Follow the following steps to execute the purpose perfectly.

Steps:

• Double-click on the sheet name to have the writing space of VBA for a sheet and input the following code.

Option Explicit
Public Sub ExtractStockData()
    Call ClearSheet
    Call UseQueryTable
End Sub
Private Sub ClearSheet()
    Dim Scorer_list As QueryTable
    For Each Scorer_list In Sheet1.QueryTables
        Scorer_list.Delete
    Next Scorer_list
    Sheet1.Cells.Clear
End Sub
Private Sub Scraping()
    Dim aA_URL As String
    aA_URL = "https://www.neogol.com/top-scorers-in-football-history-top-30/"
    Dim Scorer_list As QueryTable
    Set Scorer_list = Sheet1.QueryTables.Add("URL;" & aA_URL, Sheet1.Range("B4"))
    With Scorer_list
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "1"
        .WebFormatting = xlWebFormattingNone
        .Refresh
    End With
End Sub

• Press on the F5 button or click on Run to have the website’s data table in the selected sheet.

• You can see the output on the selected sheet. Furthermore, you can modify the scraped data according to your choice.

Read More: Excel VBA to Scrape Table from Website (With Easy Steps)

Example 2: Web Scraping Using Module Feature

There is another simple VBA code that we can use to use web scraping. In this method, you need to open a module. For further explanation, follow the steps mentioned below.

Steps:

• Click on Insert.
• From the available options, pick Module.

• Now, input the following code in that module.

Sub get_table_web()
Dim ig As Object
Dim urlc As String
urlc = "https://uk.investing.com/rates-bonds/financial-futures"
Set ig = CreateObject("InternetExplorer.Application")
ig.Visible = True
ig.Navigate urlc
Do While ig.Busy: DoEvents: Loop
Do Until ig.ReadyState = 4: DoEvents: Loop
Dim tb As HTMLTable
Set tb = ig.Document.getElementById("cr1")
Dim rowcounter As Integer
Dim columncounter As Integer
rowcounter = 4
columncounter = 1
Dim tro As HTMLTableRow
Dim tdc As HTMLTableCell
Dim thu
Dim mys As Worksheet
Set mys = ThisWorkbook.Sheets("Sheet3")
For Each tro In tb.getElementsByTagName("tr")
For Each thu In tro.getElementsByTagName("th")
mys.Cells(rowcounter, columncounter).Value = thu.innerText
columncounter = columncounter + 1
Next thu
For Each tdc In tro.getElementsByTagName("td")
    mys.Cells(rowcounter, columncounter).Value = tdc.innerText
columncounter = columncounter + 1
Next tdc
columncounter = 1
rowcounter = rowcounter + 1
Next tro
End Sub

• Now, click on Run.

• We will have our desired output on the mentioned sheet. Again, we can modify the scraped data according to our choice.

Read More: Excel VBA: Web Scraping with Chrome (With Easy Steps)

Example 3: Web Scraping with Message Box

A simple yet effective VBA code can be mentioned with what we can have the scraped data in Message Box.

Steps:

• Click on Insert and select Module.
• Afterward, write the following code in that module.

Sub Web_ScrapingVBA()
  Dim SearchEngine As InternetExplorer
  Set SearchEngine = New InternetExplorer
  SearchEngine.Visible = True
  SearchEngine.Navigate ("https://www.premierleague.com/tables")
  Do While SearchEngine.ReadyState <> READYSTATE_COMPLETE: Loop
  MsgBox SearchEngine.LocationName & vbNewLine & vbNewLine & SearchEngine.LocationURL
End Sub

• After that, press the F5 button or click on Run.

We will have the scraped data from the web in a message box.

Read More: How to Do Web Scraping Without Browser with Excel VBA

Conclusion

At the end of this article, I like to add that I have tried to explain how to use web scraping with Excel VBA with 3 suitable examples. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.