A major task most developers encounter when working with Excel VBA or any other programming language is automatically extracting data from a website. Today in this article, I will show you how you can use Excel VBA to automatically extract data from a website in quite a sophisticated and convenient way.
Excel VBA to automatically pull data from a website (Quick View)
Sub Pull_Data_from_Website() Website_Address = “https://exceldemy.com” HTML_Tag = “div” Dim Browser As New InternetExplorer Dim Doc As New HTMLDocument Dim Data As Object Browser.Visible = True Browser.navigate Website_Address Do DoEvents Loop Until Browser.readyState = READYSTATE_COMPLETE Set Doc = Browser.document Set Data = Doc.getElementsByTagName(HTML_Tag) MsgBox Data(0).innerHTML End Sub
Download practice book
Download this workbook practice to exercise while you are reading this article.
An overview of automatically extracting data from a website to Excel VBA (Step-by-Step Analysis)
Without further delay, let’s go to our main discussion today. Let’s see how we can extract data from a website to Excel VBA with a step-by-step analysis.
⧪ Step 1: Setting up the environment
First, we need to set up the environment for extracting data. Press ALT+F12 on your keyboard. The Visual Basic window will open.
Go to the toolbar at the top and click Tools > References.
This will open a dialog called References – VBAProject. Check Microsoft HTML Object Library and Microsoft Internet Controls if they are not checked.
Now the environment for extracting data is set up.
⧪ Step 2: Insert a new module
Go to the toolbar and click Insert > Module. This will open a new module named Module1 (or any other depending on your previous history).
We’ll insert the VBA code here.
⧪ Step 3: Insert the inputs
Now It’s time for code. First, we need to insert the inputs into the code. These are the address of the website and the name of the HTML tag from which we want to extract data.
We go to our address of the website is “https://www.exceldemy.com” and the HTML tag is div.
Website_Address = “https:// exceldemy.com” HTML_Tag = “div”
⧪ Step 4: Declaring the Required Objects
Next, we need to declare the required objects. They are a browser to navigate to the website, an HTML document and a data object.
Dim Browser As New InternetExplorer Dim Doc as new HTMLDocument Dim Data as object -Website-into-Excel-VBA-6.png” alt=”” />
⧪ Step 5: Navigate to the website
Next, we will navigate to the input website.
Browser.Visible = True Browser.navigate Website_Address
The line Browser.Visible = True is optional If you put this line and run the code here, the declared browser will open on your computer and take you to the indicated address.
⧪ Step 6: Get the data
Now the task more important. We will extract the necessary data from the given HTML tag using the getElementsbyTagName method of VBA.
DoEvents Loop to the browser. readyState = READYSTATE_COMPLETE Set Doc = Browser.document Set Data = Doc.getElementsByTagName(HTML_Tag)
⧪ Step 7 (optional): Display the data
You have successfully extracted the necessary data from the website as an array named Data. Now, if you want to display specific data, you can use that specific property of the getElementsbyTagName method.
For example, to display the inner HTML of the element. >1st, you can use:
MsgBox Data(0).innerHTML
Therefore, the complete VBA code will be:
⧭ VBA Code:
Sub Pull_Data_from_Website () Website_Address = “https://exceldemy.com” HTML_Tag=”div” Dim Browser As New InternetExplorer Dim Doc As New HTMLDocument Dim Data As Object Browser.Visible = True Browser.navigate Website_Address Do DoEvents Loop Until Browser.readyState = READYSTATE_COMPLETE Set Doc = Browser .document Set Data = Doc.getElementsByTagName(HTML_Tag) MsgBox Data(0).innerHTML End Sub
⧭ Result:
Run the code. Will display the inner HTML of the first div element in a message box.
2 Practical Methods to Automatically Extract Data in Excel VBA
We have learned how to automatically extract data in Excel VBA. Now, we’ll develop a macro and a user-defined function to accomplish this.
1. Developing a Macro to Automatically Extract Data in Excel VBA
Here we have a website address in cell B2 along with the names of some HTML tags in the range B4:D4.
Our object is to develop a Macro to extract the inner HTML of each tag of the website mentioned in the corresponding column.
The VBA code will be :
⧭ VBA Code:
Sub Pull_Data_from_Website() Website_Address = Range(“B2”).Value Dim Browser As New InternetExplorer Dim Doc As New HTMLDocument Dim Data As Object Set HTML_Tags = Range(“B4:D4”) For i = 1 To HTML_Tags.Columns.Count HTML_Tag = HTML_Tags.Cells(1, i) Browser.navigate Website_Address Do DoEvents Loop to Brow ser.readyState = READYSTATE_COMPLETE Set Doc = Browser.document Set Data = Doc.getElementsByTagName(HTML_Tag) For j = 1 To Data.Length HTML_Tags.Cells(j + 1, i) = Data(j – 1).innerHTML Next j Next i End Sub
⧭ Output:
Run the code (Obviously, after changing the inputs). It will extract the inner HTML of each tag in the corresponding column like this.
Read more: How to extract data from another sheet based on Excel criteria
Similar readings
- How to import Secure Website Data to Excel (with Quick Steps)
- How to Convert Excel to Text File with Pipe Delimiter (2 Ways)
- Convert Excel to Text File with Delimiter (2 Easy Approaches)
- How to Convert Notepad to Excel with Columns (5 Methods)
- How to extract data from an Excel sheet (6 effective methods)
2. Creating a User Defined Function to Automatically Extract Data in Excel VBA
Now, let’s build a User Defined Function to extract data from a given >HTML tag of a given website.
The VBA code will be:
⧭ VBA code:
Function PullData(Website_Address, HTML_Tag) Dim Browser as new Internet Explorer Dim Doc as new HTMLDocument Dim Data as Browser.navigate object Website_Address Do DoEvents Loop until Browser.readyState = READYSTATE_COMPLETE Set Doc = Browser.document Set Data = Doc .getElementsByTagName (HTML_Tag) Dim Output as variant ReDim Output(Data.Length – 1) To i = LBound(Output) To UBound(Output) Output(i) = Data(i).innerHTML Next i PullData = Output End function
⧭ Output:
The code creates a function called PullData.
Select any range of cells in your worksheet and enter the formula:
Then press CTRL + SHIFT + ENTER (Not necessary if you’re in Office 365 ).
Will extract the innerHTML from all parent tags of the website “https://www.exceldemy.com” in a range.
L You can then drag the fill handle to the right to perform the same procedure for the rest of the tags.
Read More: Transfer Data From One Excel Spreadsheet To Another Automatically
Conclusion
So, this is how to extract data automatically in VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our ExcelDemy site for more posts and updates.
Related Articles
- How to Extract Excel Data Based on Criteria (5 Ways)
- Extract Text After a Character in Excel (6 Ways)
- How to extract month from date in Excel (5 quick ways)
- How to extract year from date in Excel (3 ways)
- Return multiple values in Excel based on unique criteria (3 options)
- How to extract data from Excel to Word (4 ways)
.