CODEWITHSUNDEEP
excelvbaweb-scrapingxmlhttprequest
Lalit Patel
Lalit Patel

Reputation: 115

Extracting Data from URL VBA getting IE not suppoting

I have been using the following Excel VBA macro to bring back data from a website. It worked fine until a few days ago when the website stopped supporting IE. Of course the macro just fails now as there is no data on the webpage to bring back to Excel, Is there a way to have the "Get method" (MSXML2.XMLHTTP)

here is my Code

    Public Sub GGGG()
        Dim MSX As Object
        Dim HTML As HTMLDocument
        Dim URL As String
        Dim UrlResponse As String
        Dim N As Long
        Dim sht1, sht2 As Worksheet

       ' On Error Resume Next
        Set MSX = CreateObject("MSXML2.XMLHTTP")
        Set HTML = New HTMLDocument
        

        URL = "https://www.justdial.com/Agra/Yogi-General-Store-Opp-Eclave-Satiudum-Sadar-Bazaar/0562P5612-5612-120207212812-H5I2_BZDET"
                    
                    With MSX
                        .Open "GET", URL, False
                        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
                        .send
                        UrlResponse = StrConv(.responseBody, vbUnicode)
                    End With

                       ActiveCell.Offset(0, 1) = UrlResponse

                    
    End Sub

I get response like

Error An error occurred while processing your request.

Reference #97.ec8a2c31.1621136928.281f3ca8

Please anyone can support me how to get data when IE dose not support I am not an expert in coding

Upvotes: 0

Views: 2492

Answers (3)

SIM
SIM

Reputation: 22440

Okay, try this to get the title and votes from that site using vba in combination with selenium.

Sub FetchInfo()
    Dim driver As Object, oTitle As Object
    Dim oVotes As Object
    
    Set driver = CreateObject("Selenium.ChromeDriver")
    
    driver.get "https://www.justdial.com/Agra/Yogi-General-Store-Opp-Eclave-Satiudum-Sadar-Bazaar/0562P5612-5612-120207212812-H5I2_BZDET"
    Set oTitle = driver.FindElementByCss("span.item > span", Raise:=False, timeout:=10000)
    Set oVotes = driver.FindElementByCss("span.rtngsval > span.votes", Raise:=False, timeout:=10000)
    Debug.Print oTitle.Text, oVotes.Text
End Sub

Upvotes: 1

Gustav
Gustav

Reputation: 55806

You probably need to set the Feature Browser Emulation to zero as detailed by Daniel here:

Everything You Never Wanted to Know About the Access WebBrowser Control

That said, your URL fails even when opened in Edge Chromium, so the site may suffer from a general failure.

Upvotes: 0

Kin Siang
Kin Siang

Reputation: 2699

When the webpage no longer support IE in future, you can try out web scrape using Google Chrome with new add-in installed, please see following link for the add-in installation adn how to write in VBA. However, it is in my opinion the most simple way to perform your work is to use Uipath free community version, it work for all type of web-browser.

VBA guideline: https://www.wiseowl.co.uk/vba-macros/videos/vba-scrape-websites/web-scraping-selenium-chrome/

VBA library installation for Selenium: https://code.google.com/archive/p/selenium-vba/downloads

Upvotes: 0

Related Questions