Retrieving data from URL [Excel VBA]

Question

I have extensively searched and not found what i am looking for. My knowledge of VBA has so far been confined to manipulating numbers so this is a new area for me.

I am attempting to pull the single line of data from the following url LINK. Once that is done, I can work on parsing that data but we'll cross that bridge...

Here is the code so far in Excel VBA:

Enum READYSTATE
    READYSTATE_UNINITIALIZED = 0
    READYSTATE_LOADING = 1
    READYSTATE_LOADED = 2
    READYSTATE_INTERACTIVE = 3
    READYSTATE_COMPLETE = 4
End Enum

Sub ImportStackOverflowData()

    Dim ie As InternetExplorer

    Dim html As HTMLDocument

    Set ie = New InternetExplorer

    ie.Visible = False
    ie.navigate "http://api.rsbuddy.com/grandExchange?a=guidePrice&i=1637"

    Do While ie.READYSTATE <> READYSTATE_COMPLETE
        DoEvents
    Loop

    'show text of HTML document returned
    Set html = ie.document.body
    MsgBox html.DocumentElement.innerHTML

    'close down IE and reset status bar
    Set ie = Nothing
    Application.StatusBar = ""

End Sub

After navigating to the url, the IE downloads window opens, and asks me to Open or Save a .json file... clicking anything crashes the code and ignoring it leaves the code in the READYSTATE loop below it.

How can I stop it from opening this window, and instead insert the info into the workbook?

Thank you.

Answer 1

Getting around that box isn't trivial, there are many, many posts about it if you search. It gets especially complex since some versions of IE act in a different way (with the download ribbon rather than a dialog box).

To do it the way you are doing it, you need to use Windows APIs to get the handle of the download window and dismiss it. This gets very complicated very quickly.

There is however a much simpler way; don't use Internet Explorer for simple requests. Use something lighter, I've use MSXML here, but you could swap it with WINHTTP.

Sub ImportStackOverflowData()

    Dim request As Object
    Set request = CreateObject("MSXML2.XMLHTTP")

    With request
        .Open "GET", "http://api.rsbuddy.com/grandExchange?a=guidePrice&i=1637", False
        .send
        MsgBox .responseText
    End With

End Sub