Fetch Product Price and Name from Amazon with VBA

Question

So I finally have to ask this from an expert after I found totally stuck in it. Here is the Macro Written by me. The main purpose is to fetch the Product Price and Product Name from Amazon Website and paste it in Excel column. All i need to add ASIN number of Product in column A1 manually. It will automatically go to the given link and collect data and paste it in respective columns i.e., column B1 and C1.

This is the example URL. I am trying to get the data from this URL for now.

https://www.amazon.co.uk/Alessi-Colombina-Soup-Plate-FM10/dp/B0012620YA/ref=sr_1_1?dchild=1&keywords=B0012620YA&qid=1603405464&sr=8-1

I tried to find the class of Price and Name in InspectElement, but I didn't find any class or ID working there. When I run macro it goes to site and loads for sometime but it doesn't fetch Product Price or Name from there and its totally unpredictable why it's not working.

I tried using getElementsbyClassName("") and getElementsbyID(""). All not working.

Is there any error ( logical ) in my code or I need to rewrite it? Actually I am building a macro. Any help from Experts is very appreciable. I have Microsoft HTML Object Library & Internet Controls Checked.

Here is the code i am using:

Sub useClassnames()

Dim element As IHTMLElement
Dim elements As IHTMLElementCollection
Dim IE As InternetExplorer

Dim html As HTMLDocument

Set IE = New InternetExplorer

IE.Visible = True

IE.navigate "https://www.amazon.co.uk/Alessi-Colombina-Soup-Plate-FM10/dp/B0012620YA/ref=sr_1_1?dchild=1&keywords=B0012620YA&qid=1603405464&sr=8-1"

'Wait until IE has loaded the web page

Do While IE.readyState <> READYSTATE_COMPLETE

Application.StatusBar = " Loading Web page … "

DoEvents

Loop

Set html = IE.document

Set elements = html.getElementsByClassName(" priceblock_ourprice ")

'Here is the className "priceblock_ourpirce" I found it in InspectElement but its not getting any data. I am not able to find the 'correct ClassName for Price and Name of Product from InspectElemnt. As I am not a web developer.

Dim count As Long

Dim erow As Long

count = 0

For Each element In elements

If element.className = “priceblock_ourprice” Then

erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row

Cells(erow, 1) = html.getElementsById(“priceblock_ourprice”)(count).innerValue

Cells(erow, 2) = html.getElementsById(" productTitle ")(count).innerText

count = count + 1

End If

Next element

Range("A2:B2").Select

Columns("A:A ").EntireColumn.AutoFit ""

Columns("B:B").ColumnWidth = 36

MsgBox count

End Sub

Answer 1

I think the problem is not to scrape the values you want. I Think you have to learn another way to get information how you can scrape values from webpages.

You try to get the information about class names and IDs by inspecting the elements. A better way is, to press F12 in the browser (but don't use the IE for that). I use FireFox. After pressing F12 different tools open at the bottom of the page. The first tab of this area is "Inspector".

You can see the HTML code. But it is organized with arrows you can click to enlarge HTML code for the different elements. When you place the mouse over the different lines of HTML you can see in the upper area a blue overlay of the corresponding element. Now you can click through the arrow levels until you reach the element you search for.

Look there for an ID or class name. If there is nothig you can use to scrape the element it is helpfull to look the HTML levels obove to fence the HTML you are working with. For your little project, you can use IDs to scrape title and price.

Before I show you the VBA code to do that, two more things:

1. You wrote, you only need the ASIN. That is right but you don't do that in your code. You use a url with the title you wana scrape. But I have good news for you. You can use realy only the ASIN and nothing else: https://www.amazon.co.uk/dp/B0012620YA shows the same page like https://www.amazon.co.uk/Alessi-Colombina-Soup-Plate-FM10/dp/B0012620YA/ref=sr_1_1?dchild=1&keywords=B0012620YA&qid=1603405464&sr=8-1

2. On Amazon often different sellers with different prices use the same offer. Look in your linked offer for the line: New (3) from £129.00 + FREE Shipping If you click the link a page with the seller overview will open. If you need all the prices and sellernames, you have to do a lot more work.

Here is the VBA code for scraping the title and the price which is shown in the offer:

Sub ScrapeAmazonOffers()

Dim url As String
Dim ie As Object
Dim nodeTitle As Object
Dim nodePrice As Object
Dim resultExample As String

  'Amazon offer url, only with ASIN
  url = "https://www.amazon.co.uk/dp/B0012620YA"

  'Initialize Internet Explorer, set visibility,
  'call URL and wait until page is fully loaded
  Set ie = CreateObject("InternetExplorer.Application")
  ie.Visible = True
  ie.navigate url
  Do Until ie.readyState = 4: DoEvents: Loop
  
  'Get offer title
  Set nodeTitle = ie.document.getElementByID("productTitle")
  resultExample = nodeTitle.innertext
  
  'Get price
  Set nodePrice = ie.document.getElementByID("priceblock_ourprice")
  resultExample = resultExample & Chr(13) & nodePrice.innertext
  
  'Clean up
  ie.Quit
  Set ie = Nothing
  Set nodeTitle = Nothing
  Set nodePrice = Nothing
  
  'Show result for this example
  MsgBox resultExample
End Sub

Answer 2

GetElement s ByID

• I wrote the function getElementsByID the other day. The procedure testGEBI shows how to use it.
• My knowledge of this subject is very limited, so be aware that the function may produce unwanted results. In my usage so far, it failed with an error message if there was no internet connection, or if an ID did not exist.
• The current setup will write Alessi Colombina Soup Plate, Set of 6 (FM10/2) to A2 and £129.00 to B2 and will then autofit columns A and B.

The Code

Option Explicit

Sub testGEBI()
    
    Const First As String = "A2"
    Const URL  As String = "https://www.amazon.co.uk/Alessi-Colombina-Soup-" _
                         & "Plate-FM10/dp/B0012620YA/ref=sr_1_1?dchild=1&" _
                         & "keywords=B0012620YA&qid=1603405464&sr=8-1"
    Dim Elements As Variant
    Elements = Array("productTitle", "priceblock_ourprice")
    
    Dim Data As Variant
    Data = getElementsByID(Elements, URL)
    
    With Range(First)
        .Resize(, UBound(Data, 2)).Value = Data
        Columns(.Column).Resize(, UBound(Data, 2)).AutoFit
    End With

End Sub

Function getElementsByID(Elements As Variant, _
                         ByVal URL As String, _
                         Optional ByVal getColumn As Boolean = False) _
         As Variant
    
    Dim rText As String
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "text/xml"
        .Send
        rText = .responseText
    End With
        
    Dim j As Long
    With CreateObject("htmlfile")
        .body.innerHTML = rText
        Dim Offs As Long
        Offs = LBound(Elements) - 1
        Dim ElementsCount As Long
        ElementsCount = UBound(Elements) - Offs
        Dim Data As Variant
        If Not getColumn Then
            ReDim Data(1 To 1, 1 To ElementsCount)
            For j = 1 To ElementsCount
                Data(1, j) = WorksheetFunction _
                  .Trim(.getElementById(Elements(j + Offs)).innerText)
            Next j
        Else
            ReDim Data(1 To ElementsCount, 1 To 1)
            For j = 1 To ElementsCount
                Data(j, 1) = WorksheetFunction _
                  .Trim(.getElementById(Elements(j + Offs)).innerText)
            Next j
        End If
    End With
    
    getElementsByID = Data
    
End Function