Fetch Product Price And Name From Amazon With VBA

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 fro

Solution 1:

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

Solution 2:

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