VBA Issue in Web Scraping

Question

I'm trying to get the Product name, SKU number, New and Old price from "https://www.crateandbarrel.me/en-ae/search/sofas" webpage using below VBA but nothing is fecthing. It seens the HTML data which was fetched doesn't contain the information which I am requesting.

Sub webscrape()

Dim HTTPreq As New MSXML2.XMLHTTP60
Dim html As HTMLDocument
Dim url As String

url = "https://www.crateandbarrel.me/en-ae/search/sofas"

'send HTTP request to url

With HTTPreq
    .Open "Get", url, False
    .send
End With

response = HTTPreq.responseText

Debug.Print response

'read response html document

Set html = CreateObject("htmlfile")
html.body.innerHTML = response

r = 1
For Each divElement In html.getElementsByClassName("container container-grid")
    r = r + 1
    Set divCollection = divElement.all
    For Each element In divCollection
        If InStr(element.className, "name") > 0 Then Range("A" & r).Value = element.innerText
        If element.className = "col-itemSKU -inner" Then Range("B" & r).Value = element.innerText
        If element.className = "price state-cross" Then Range("D" & r).Value = element.innerText
        If element.className = "crossed-price" Then Range("E" & r).Value = element.innerText
   Next element
Next divElement

 
End Sub

Looking at the page, a lot of the data you want seems to be loaded dynamically. You (probably) wont be able to use XMLHTTP request. I would suggest using the InternetExplorer object (but its too old, and it doesnt work, tried it..) so you'll need something a bit more modern. selenium webdriver (edge or chrome) works well with excel — NickSlash
– NickSlash, Commented Oct 3, 2022 at 11:54

SIM · Accepted Answer · 2022-10-03 19:12:32Z

1

Although the site's content is highly dynamic, it is loaded using an endpoint via ajax request, from which you can retrieve the JSON response by issuing an XMLHTTP request complying with the following method.

You can, however, fetch your required fields from the response using any json converter like VBA-JSON

Sub ScrapeContent()
    Const Url$ = "https://api.crateandbarrel.me/rest/v2/cab/products/search"
    Dim Html As HTMLDocument, sParams$
    Dim oHttp As Object
    
    sParams = "fields=products(code%2CearnablePoints%2Csellable%2Cname%2CurlName%2Csummary%2Cprice(FULL)%2Cbadges(code%2Cname)%2Cimages(DEFAULT)%2Cstock(FULL)%2CaverageRating%2CcrossedPrice(FULL)%2Ccategories(name%2Ccode%2Curl)%2Cswatches(FULL)%2Cvariants(FULL)%2CprimaryCategory(FULL))%2Cfacets%2Cpagination(DEFAULT)%2Csorts(DEFAULT)%2CfreeTextSearch&query=sofas&currentPage=1&pageSize=24&lang=en&curr=AED"

    Set Html = New HTMLDocument
    Set oHttp = CreateObject("MSXML2.XMLHTTP")
    
    With oHttp
        .Open "GET", Url & "?" & sParams, True
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36"
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send
        While .readyState < 4: DoEvents: Wend
        MsgBox .responseText
    End With
End Sub

edited Oct 3, 2022 at 19:12

answered Oct 3, 2022 at 18:57

SIM

22.5k6 gold badges45 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nooh_Noah Over a year ago

Guy's thank you so much, I was able to scrape the detials from there USA website which was not so dynamic. Now I need to fetch the images or image url atleast in excel.

Collectives™ on Stack Overflow

VBA Issue in Web Scraping

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related