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