Hi all,

I've written a code that actually works but is really slow and can be improved to make it less error sensitive and faster.

It's a code that uses IE automation in Excel VBA and extracts store prices from a supermarket website. The thing is that across the different stores (in different cities) the prices are different for the same products. To extract the different 'price lines' for the products the codes does/ must do the following:

1- visit the website of the supermarket and select the first store / city. The selected store is saved in a browser cookie.
2- the next code part visits the specified product (in total, finally it will be a fixed list around 200 products, using a loop). (The prices are extracted in a worksheet, not includes in this code yet)
3- next a second city/supermarket is selected and stored in the browser cookie
4- the same code (#2, see above) is called and the prices for the same 200 products is scraped from the store, and saved.


When using IE automation the specific store/city is remembered when visiting the product pages. I found that when using a different method like XMLHTTP request the code is much faster but the problem then is that the product page is not linked to the store that was saved in a browser cookie. Any suggestions how to use the XMLHHTP code (provided at the bottom) or a different method together with a cookie in which the correct store is saved while scraping the product prices?


Below the code, it's now using only one store and only one sample product to illustrate.

Selecting the Store using IE automation and saving it in browser cookie (using IE automation)

This code will set the value of the browser cookie named/stored at "www.jumbo.com > cookies > HomeStore" to the value "YC8KYx4XB88AAAFIDcIYwKxJ"
If there is a cleaner/faster way to only set/change this value, it would would help already for this part


    Sub SetStore()
    
    ' this code will set the value of the cookie named "www.jumbo.com > cookies > HomeStore" to the value "YC8KYx4XB88AAAFIDcIYwKxJ"
    ' when using sample store 2 (Brielle) it should be set to "OG8KYx4XP4wAAAFIlsEYwKxK"
        Dim IE As New SHDocVw.InternetExplorer
        Dim HTMLDoc As MSHTML.HTMLDocument
        Dim HTMLSearchbox As MSHTML.IHTMLElement
        Dim HTMLSearchboxes As MSHTML.IHTMLElementCollection
        Dim HTMLButton As MSHTML.IHTMLElement
        Dim HTMLButtons As MSHTML.IHTMLElementCollection
        Dim HTMLSearchButton As MSHTML.IHTMLElement
        Dim HTMLSearchButtons As MSHTML.IHTMLElementCollection
        Dim HTMLStoreID As MSHTML.IHTMLElement
        Dim HTMLStoreIDs As MSHTML.IHTMLElementCollection
        Dim HTMLSaveStore As MSHTML.IHTMLElement
        Dim HTMLSaveStores As MSHTML.IHTMLElementCollection
    
       'set on False to hide IE screen
        IE.Visible = True
    
        'navigate to url with limited content
        IE.navigate "https://www.jumbo.com/content/algemene-voorwaarden/"
    
        Do While IE.readyState <> READYSTATE_COMPLETE
        Loop
        Set HTMLDoc = IE.document
        Set HTMLButtons = HTMLDoc.getElementsByTagName("button")
        
        For Each HTMLButton In HTMLButtons
    
            If HTMLButton.getAttribute("data-jum-action") = "openHomeStoreFinder" Then
               HTMLButton.Click
                Exit For
            End If
         Next HTMLButton
    
        Application.Wait Now + #12:00:02 AM#
        
        Set HTMLSearchboxes = HTMLDoc.getElementsByTagName("input")
        For Each HTMLSearchbox In HTMLSearchboxes
         
         If HTMLSearchbox.getAttribute("id") = "searchTerm__DkKYx4XylsAAAFJktpb2Guy" Then
    
        'input field store name/location to show search results
        ' Sample 1 "Oosterhout Zuiderhout"
        ' Sample 2 "Brielle"
        ' change value below to search correct store (also change corresponding value later in this code)
        HTMLSearchbox.Value = "Oosterhout Zuiderhout"
    
        Application.Wait Now + #12:00:03 AM#
        HTMLSearchbox.Click
                Exit For
            End If
    
         Next HTMLSearchbox
         Set HTMLSearchButtons = HTMLDoc.getElementsByTagName("button")
        
        For Each HTMLSearchButton In HTMLSearchButtons
    
            If HTMLSearchButton.getAttribute("data-jum-filter") = "search" Then
                HTMLSearchButton.Click
                Exit For
            End If
    
        Next HTMLSearchButton
    
        Application.Wait Now + #12:00:05 AM#
    
        Set HTMLStoreIDs = HTMLDoc.getElementsByTagName("li")
        
        For Each HTMLStoreID In HTMLStoreIDs
      ' Sample 1 oosterhout = YC8KYx4XB88AAAFIDcIYwKxJ
      ' Sample 2 brielle = OG8KYx4XP4wAAAFIlsEYwKxK
    'change value below to switch store
         If HTMLStoreID.getAttribute("data-jum-store-id") = "YC8KYx4XB88AAAFIDcIYwKxJ" Then
    
         HTMLStoreID.Click
          Application.Wait Now + #12:00:03 AM#
              Exit For
          End If
    
      Next HTMLStoreID
    
      Set HTMLSaveStores = HTMLDoc.getElementsByTagName("button")
    
      For Each HTMLSaveStore In HTMLSaveStores
    
            If HTMLSaveStore.getAttribute("data-jum-action") = "saveHomeStore" Then
                HTMLSaveStore.Click
                Exit For
           End If
    
    
        Next HTMLSaveStore
    
       'IE.Quit
    
    End Sub

Extracting the product price (using IE automation)

The following part will extract the price (via debug.print in Immediate window) for one sample product (Bastogne Koeken).
When using the first store (Oosterhout Zuiderhout) the price is 1,35 for this product.
When using the second store (Brielle) the price is 1,44 (prices might change over time, but should be different between the two stores).
When no store is selected (no HomeStore cookie value is set) the price is 1,44 

Sub GetJumboPriceIE()


Dim IE As New SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
Dim JumInputs As MSHTML.IHTMLElementCollection
Dim JumInput As MSHTML.IHTMLElement
Dim JumPrice As MSHTML.IHTMLElement
Dim JumboPrice As Double
Dim Price_In_Cents_Tag As String


Dim SKU_tag As String, SKU_url As String


SKU_tag = "173140KST"
SKU_url = "https://www.jumbo.com/lu-bastogne-koeken-original-260g/173140KST/"


IE.Visible = False
   IE.navigate SKU_url


    Do While IE.readyState <> READYSTATE_COMPLETE
    Loop


    Set HTMLDoc = IE.document


    IE.Quit

Set JumInputs = HTMLDoc.getElementsByTagName("input")


Price_In_Cents_Tag = "PriceInCents_" & SKU_tag


Set JumPrice = HTMLDoc.getElementById(Price_In_Cents_Tag)


JumboPrice = JumPrice.getAttribute("value") / 100
Debug.Print JumboPrice


End Sub

As said, the code is working but is slow due to the IE automation it's taking very long when using it for 200 products and 5 different stores.

Below a code for the price extraction part that is much faster (using XML HTTP method) but doesn't work because i need to set the correct store in the cookie first and i cannot figure it how i do this. Any help is welcome!


Extracting the price using XML HTTP method
The following code uses XML HTTP request to get the price. No store is selected or used from a cookie, so 1,44 is the extracted price. I cannot get the 1,35 from the Oosterhout Zuiderhout store when using the XML HTTP method. >> any suggestions?

Sub GetJumboPriceXML()


Dim XMLReq As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument


Dim JumInputs As MSHTML.IHTMLElementCollection
Dim JumInput As MSHTML.IHTMLElement
Dim JumPrice As MSHTML.IHTMLElement
Dim JumboPrice As Double
Dim Price_In_Cents_Tag As String


Dim SKU_tag As String, SKU_url As String


SKU_tag = "173140KST"
SKU_url = "https://www.jumbo.com/lu-bastogne-koeken-original-260g/173140KST/"


XMLReq.Open "GET", SKU_url, False
XMLReq.send


If XMLReq.Status <> 200 Then


MsgBox "Problem" & vbNewLine & XMLReq.Status & " - " & XMLReq.statusText
 Exit Sub
 End If


  HTMLDoc.body.innerHTML = XMLReq.responseText


Set JumInputs = HTMLDoc.getElementsByTagName("input")

Price_In_Cents_Tag = "PriceInCents_" & SKU_tag

Set JumPrice = HTMLDoc.getElementById(Price_In_Cents_Tag)

JumboPrice = JumPrice.getAttribute("value") / 100
Debug.Print JumboPrice


End Sub

Thanks so much for any help, please let me know if i can clarify any further.