I am trying to pull seller information from Amazon page with price by automating web browser. I am trying to run the below code, but the error I am getting is:
Object Variable or With Block variable not set.
Can someone guide me where i am going wrong.
Option Explicit Sub RunNewModule() Dim ie As InternetExplorer Dim html As HTMLDocument Set ie = CreateObject("InternetExplorer.Application") ie.Visible = False ie.Navigate "//www.amazon.com/gp/offer-listing/B00SVA81Z2/ref=dp_olp_new_mbc?ie=UTF8&condition=new" Dim priceData As Variant Dim sellerdata As Variant Dim item As Variant Dim cntr As Integer priceData = html.getElementsByClassName("olpOfferPrice").getElementsByTagName("span")(0).innerText cntr = 1 For Each item In priceData Range("B" & cntr) = item.innerText cntr = cntr + 1 Next item sellerdata = html.getElementsByClassName("olpSellerName").getElementsByTagName("span")(0).innerText cntr = 1 For Each item In sellerdata Range("A" & cntr) = item.innerText cntr = cntr + 1 Next item End Sub
Reza Aghaei
116k16 gold badges191 silver badges370 bronze badges
asked Dec 25, 2015 at 14:07
Sumeet PujariSumeet Pujari
1811 gold badge5 silver badges19 bronze badges
6
You didn't assign html and it's null now.
You should assign it this way:
Set html= ie.DocumentTo get an element by it's class name:
Dim ie As InternetExplorer Dim html As IHTMLDocument Set ie = CreateObject("InternetExplorer.Application") ie.Visible = False ie.Navigate "//stackoverflow.com/questions/34463544/vba-fetching-data-from-class-name" While ie.Busy DoEvents Wend While ie.ReadyState < 4 DoEvents Wend Set html = ie.Document Dim elements As IHTMLElementCollection Set elements = html.getElementsByClassName("question-hyperlink") If elements.Length > 0 Then MsgBox elements(0).innerText End If ie.Quit Set ie = NothingDon't forget to add reference to:
- Microsoft Internet Controls
- Microsoft Html Object library
For that amazon link:
answered Dec 25, 2015 at 14:41
Reza AghaeiReza Aghaei
116k16 gold badges191 silver badges370 bronze badges
5
An important task that most developers encounter while working with Excel VBA or any other programming language is to pull data automatically from a website. Today in this article, I’ll show you how you can use Excel VBA to pull data automatically from a website quite sophistically and conveniently.
Excel VBA to Pull Data Automatically from a Website (Quick View)
Sub Pull_Data_from_Website() Website_Address = "//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 SubDownload Practice Workbook
Download this practice workbook to exercise while you are reading this article.
An Overview to Pull Data Automatically from a Website into Excel VBA (Step-by-Step Analysis)
Without further delay, let’s go to our main discussion today. Let’s see how we can pull data from a website into Excel VBA with step-by-step analysis.
⧪ Step 1: Setting up the Environment
First of all, we have to set up the environment to pull data. Press ALT+F12 on your keyboard. The Visual Basic window will open.
Go to the toolbar at the top and click on Tools > References.
A dialogue box called References – VBAProject will open. Check Microsoft HTML Object Library and Microsoft Internet Controls if they are unchecked.
Now the environment for pulling data is set.
⧪ Step 2: Inserting a New Module
Go to the toolbar and click on Insert > Module. A new module called Module1 (Or anything other depending on your past history) will open.
We’ll insert the VBA code here.
⧪ Step 3: Inserting the Inputs
Now it’s time for the code. First of all, we have to insert the inputs into the code. These are the website address and the name of the HTML tag from which we want to scrape data.
Let’s our website address is “//exceldemy.com” and the HTML tag is div.
Website_Address = "//exceldemy.com" HTML_Tag = "div"⧪ Step 4: Declaring the Necessary Objects
Next, we have to declare the necessary objects. They are a browser for navigating to the website, an HTML Document, and a data object.
Dim Browser As New InternetExplorer Dim Doc As New HTMLDocument Dim Data As Object⧪ Step 5: Navigating to the Website
Next, we’ll navigate to the input website.
Browser.Visible = True Browser.navigate Website_AddressThe line <b><span style="color: #ff6600;">Browser.Visible = True</span> </b>is optional. If you put this line and run the code here, the declared browser will open on your computer taking you to the given address.
⧪ Step 6: Pulling the Data
Now the most important task. We’ll extract the necessary data from the given HTML tag using the getElementsbyTagName method of VBA.
Do DoEvents Loop Until Browser.readyState = READYSTATE_COMPLETE Set Doc = Browser.document Set Data = Doc.getElementsByTagName(HTML_Tag)⧪ Step 7 (Optional): Displaying the Data
You have successfully pulled the necessary data from the website as an array called Data. Now if you want to display any specific data, you can use that specific property of the getElementsbyTagName method.
For example, to display the inner HTML of the 1st element, you can use:
MsgBox Data(0).innerHTMLTherefore, the complete VBA code will be:
⧭ VBA Code:
Sub Pull_Data_from_Website() Website_Address = "//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⧭ Output:
Run the code. It’ll display the inner HTML of the 1st div element in a Message Box.
2 Handy Approaches to Pull Data Automatically into Excel VBA
We’ve learned how to pull data automatically into Excel VBA. Now, we’ll develop a Macro and a User-Defined function to accomplish this.
1. Developing a Macro to Pull Data Automatically into Excel VBA
Here we’ve got a website address in cell B2 along with the names of some HTML tags in range B4:D4.
Our object is to develop a Macro to pull the inner HTML of each tag from the mentioned website 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 Until Browser.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’ll pull the inner HTML of each tag in the corresponding column like this.
Read More: How to Pull Data From Another Sheet Based on Criteria in Excel
Similar Readings
- How to Import Data from Secure Website 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 Excel Sheet (6 Effective Methods)
2. Creating a User-Defined Function to Pull Data Automatically into Excel VBA
Now, we’ll develop a User-Defined function to pull data of a given HTML tag from a given website.
The VBA code will be:
⧭ VBA Code:
Function PullData(Website_Address, HTML_Tag) Dim Browser As New InternetExplorer Dim Doc As New HTMLDocument Dim Data As Object Browser.navigate 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) For 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 are in Office 365).
It’ll pull the innerHTML of all the head tags of the website “//exceldemy.com” in a range.
Then you can drag the Fill Handle rightward to repeat the same procedure for the rest of the tags.
Read More: Transfer Data from One Excel Worksheet to Another Automatically
Conclusion
So, this is the way to pull data automatically into VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Related Articles
- How to Extract Data from Excel 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 Single Criteria (3 Options)
- How to Extract Data from Excel to Word (4 Ways)