VBA – Get Information From a Web Page – Excel Macro – Amazon Example

This is a basic VBA code to get started with InternetExplorer.Application object and work with web browsers and data extraction. This code is written strictly for learning proposes and should be used as such. Everything is well commenced out so it should be really easy to understand. In this example we use Internet Explorer to open a product page on Amazon and we extract the page title & product price and add the data to our spreadsheet.


Sub Basics_Of_Web_Macro()

    Dim myIE As Object
    Dim myIEDoc As Object

    'Start Internet Explorer
    Set myIE = CreateObject("InternetExplorer.Application")

    'if you want to see the window set this to True
    myIE.Visible = False

    'Now we open the page we'd like to use as a source for information
    myIE.Navigate "http://www.amazon.com/gp/product/B00J34YO92/ref=s9_ri_gw_g421_i1?pf_rd_m=ATVPDKIKX0DER&pf_rd_s=desktop-3&pf_rd_r=090F56JZ7KPTB48JWMDW&pf_rd_t=36701&pf_rd_p=2090151042&pf_rd_i=desktop"

    'We wait for the Explorer to actually open the page and finish loading    
    While myIE.Busy

    'Now lets read the HTML content of the page        
    Set myIEDoc = myIE.Document

    'Time to grab the information we want            

        'We'll start with the Title of the page        
        Range("A1") = myIEDoc.Title
        'Then we'll get something from teh inner page content by using the ID        
        Range("B1") = myIEDoc.getElementById("priceblock_ourprice").innerText

End Sub

Posted by Excel Instructor: