VBA Web Scraping to Excel

What is Data Scraping?

Data scraping is the technique that helps extract desired information from an HTML web page to a local file on your machine. Normally, that local file is an Excel file, a Word file, or any Microsoft Office application. It helps channel critical information from the web page.

Data scraping becomes valuable on a research-based project that depends on the internet daily. To illustrate, consider a day trader who runs an Excel macro to pull market information from a finance website into an Excel sheet using VBA.

How to Prepare the Excel Macro Before Data Scraping (Internet Explorer)

There are certain prerequisites that must be performed on the Excel macro file before starting the data scraping process.

These prerequisites are as follows:

Step 1) Open an Excel-based macro and access the Developer option of Excel.

Step 2) Select the Visual Basic option under the Developer ribbon.

Step 3) Insert a new module.

Step 4) Initialize a new subroutine.

Sub test()
End Sub

The module would result as follows:

Step 5) Access the Reference option under the Tools tab and reference the Microsoft HTML Object Library and Microsoft Internet Controls.

The following files must be referenced to the module, as they help open Internet Explorer and facilitate macro scripting.

Now the Excel file is ready to interact with Internet Explorer. The next step is to incorporate macro scripts that facilitate data scraping in HTML.

How to Open Internet Explorer Using Excel VBA

Step 1) Initialize the variable in the subroutine as shown below.

Sub test()
Dim ie As New InternetExplorer 
Dim doc As New HTMLDocument

Step 2) To open Internet Explorer using VBA, write ie.Visible = True and press F5.

Sub test()
Dim ie As New InternetExplorer 
Dim doc As New HTMLDocument
ie.Visible = True

The module would look as follows:

How to Open a Website in Internet Explorer Using VBA

Here are the steps to open a website in Internet Explorer using VBA.

Step 1) Once you can access Internet Explorer using Excel VBA, the next step is to access a website using VBA. This is facilitated by the Navigate attribute, where the URL is passed as double quotes. Follow the steps shown.

Sub test()
Dim ie As New InternetExplorer
Dim doc As New HTMLDocument
Dim ecoll As Object
ie.Visible = True
ie.Navigate "https://demo.guru99.com/test/web-table-element.php"
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

Step 2) Press F5 to execute the macro. The following webpage would open as shown.

Now the Excel macro is ready to perform the scraping functions. The next step shows how the information can be extracted from Internet Explorer using VBA.

How to Scrape Information from a Website Using VBA

Suppose the day trader wants to access the data from the website daily. Each time the day trader clicks the button, it should auto-pull the market data into Excel.

From the above website, it is necessary to inspect an element and observe how the data is structured.

Step 1) Access the source code of the HTML below by pressing Control + Shift + I.

<table class="datatable">
<thead>
<tr>
<th>Company</th>
<th>Group</th>
<th>Pre Close (Rs)</th>
<th>Current Price (Rs)</th>
<th>% Change</th>
</tr>

The source code would be as follows:

Sub test()
Dim ie As New InternetExplorer
Dim doc As New HTMLDocument
Dim ecoll As Object
ie.Visible = True
ie.Navigate "https://demo.guru99.com/test/web-table-element.php"
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE
Set doc = ie.document

As you can see, the data is structured as a single HTML table. Therefore, to pull the entire data from the HTML table, you must design a macro that collects the data as a collection.

The collection is then pasted into Excel. To achieve the desired results, perform the steps below.

Step 2) Initialize the HTML document in the subroutine.

Step 3) Initialize the collection element present in the HTML document.

Sub test()
Dim ie As New InternetExplorer
Dim doc As New HTMLDocument
Dim ecoll As Object
ie.Visible = True
ie.Navigate "https://demo.guru99.com/test/web-table-element.php"
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE
Set doc = ie.document
Set ecoll = doc.getElementsByTagName("table")

Step 4) Initialize the Excel sheet cells with the help of a nested loop as shown.

Sub test()
Dim ie As New InternetExplorer
Dim doc As New HTMLDocument
Dim ecoll As Object

ie.Visible = True
ie.Navigate "https://demo.guru99.com/test/web-table-element.php"
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE
Set doc = ie.document
Set ecoll = doc.getElementsByTagName("table")

Excel can be initialized using the Range attribute or the Cells attribute of the sheet. To reduce the complexity of the VBA script, the collection data is initialized to the Cells attribute of Sheet 1 in the workbook.

Once the macro script is ready, assign the subroutine to an Excel button and exit the VBA module. Label the button as Refresh or any suitable name. For this example, the button is labeled Refresh.

Step 5) Press the Refresh button to get the output below.

Step 6) Compare the results in Excel with the results in Internet Explorer.

FAQs