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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Web Scraping in Excel VBA

  • Now, choose the References… option from Tools.

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

Web Scraping in Excel VBA

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

Scraping a Table from Web with Excel VBA

  • 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.

Scraping a Table from Web with Excel VBA

Read More: Excel VBA to Scrape Table from Website


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.

Web Scraping Using Module Feature

  • 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.

Web Scraping in Excel VBA

  • 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


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

Web Scraping in Excel VBA

  • 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


Download Practice Workbook


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.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly 2 years. Currently serving as an Excel and VBA Content Developer, Arif has authored over 120 articles. His expertise lies in Microsoft Office Suite, and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and ongoing enthusiasm for expanding his knowledge in data analysis.

Designation

Excel &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo