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 AsString = "A2"Const URL  AsString = "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

FunctiongetElementsByID(Elements As Variant, _
                         ByVal URL AsString, _
                         Optional ByVal getColumn AsBoolean = False) _AsVariantDimrTextAsStringWithCreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "text/xml"
        .SendrText = .responseTextEndWithDimjAsLongWithCreateObject("htmlfile")
        .body.innerHTML = rTextDimOffsAsLongOffs = LBound(Elements) - 1
        DimElementsCountAsLongElementsCount = UBound(Elements) - OffsDimDataAsVariantIfNotgetColumnThenReDimData(1 To 1, 1 To ElementsCount)
            Forj = 1 ToElementsCountData(1, j) = WorksheetFunction_
                  .Trim(.getElementById(Elements(j + Offs)).innerText)
            NextjElseReDimData(1 To ElementsCount, 1 To 1)
            Forj = 1 ToElementsCountData(j, 1) = WorksheetFunction_
                  .Trim(.getElementById(Elements(j + Offs)).innerText)
            NextjEndIfEndWithgetElementsByID = DataEndFunction

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 AsStringDim ie AsObjectDim nodeTitle AsObjectDim nodePrice AsObjectDim resultExample AsString'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 loadedSet ie = CreateObject("InternetExplorer.Application")
  ie.Visible = True
  ie.navigate url
  DoUntil ie.readyState = 4: DoEvents: Loop'Get offer titleSet nodeTitle = ie.document.getElementByID("productTitle")
  resultExample = nodeTitle.innertext
  
  'Get priceSet nodePrice = ie.document.getElementByID("priceblock_ourprice")
  resultExample = resultExample & Chr(13) & nodePrice.innertext
  
  'Clean up
  ie.Quit
  Set ie = NothingSet nodeTitle = NothingSet nodePrice = Nothing'Show result for this example
  MsgBox resultExample
EndSub