Dim MyWeb As Object, C As Integer Set MyWeb = CreateObject("InternetExplorer.Application") MyWeb.Visible = False MyWeb.navigate ("https://www.snapdeal.com/search?keyword=iphone&santizedKeyword=mobile+phone&sort=rlvncy") Do DoEvents Loop Until MyWeb.readyState = 4 On Error Resume Next Dim Mypage As HTMLDocument Set Mypage = MyWeb.document For C = 0 To 100 Set MyData = Mypage.getElementsByClassName("product-title").Item(C) Cells(C + 2, 1).Value = MyData.innerText Set MyData = Mypage.getElementsByClassName("product-price-row").Item(C) Cells(C + 2, 2).Value = MyData.innerText Cells(C + 3, 2).Select Mypage.parentWindow.scrollBy 0, 99999 Next C Set MyWeb = Nothing MsgBox "Done"
Excel VBA Code for Web Scraping
Excel is amazing anyway. You are no stranger to Excel. You use it for various purposes. However, you have perhaps not yet explored how to capitalize on its potential for web scraping.
As you know, Excel is a fantastic tool to deal with data in a structured format! Why Excel is great is because it cannot only carry out basic data computations but also perform data collection as well as analysis.
There is always a huge amount of data that needs to be scraped. It is this data that needs to be analyzed for new insights and business strategy. Where Excel comes in handy is that we need to import into Excel in order to analyze it.
When you use Excel as a web scraper tool, what it does is that it saves a lot of time and energy to scrape web data BUT YOU CAN’T LEARN HOW THEY DID.
So, here i am going to explain how to extract any website data using excel vba code. I have explain the use and run the above code in given video, request you watch it before using code.
Step 1. Watch video
Step 2. Open Excel Sheet and Insert Command button from developer tab
Step 3. Double Click on button and paste (above) code in middle section of VBA click procedure.
Step 4. Select HTML Object and Internet Control Reference Library.
Step 5. Go to in Excel and disable design mode in Developer Tab
Step 6. Click on Button to get Data.