Trying to extract Wikipedia JSON data using Excel VBA

Question

I've got a column of terms in Excel, from which I need to extract the first sentence from Wikipedia. Wiki has an API for this and this provides me exactly what I need: https://en.wikipedia.org/w/api.php?format=json&action=query&prop=extracts&explaintext=&exintro=&exsentences=1&&titles=Stack%20Overflow under "extract".

I've installed VBA-JSON, my code is below but I'm not having any luck; I tried following examples here Extract JSON in Excel VBA but I received errors in regards to the 'results' object.

     Dim http As Object
     Dim term As String
     x = 2
     Set http = CreateObject("MSXML2.XMLHTTP")

     Do While Sheet2.Cells(x, 4) <> ""
     term = Sheet2.Cells(x, 4)

     Const sURL As String = "https://en.wikipedia.org/w/api.php?format=json&action=query&prop=extracts&explaintext=&exintro=&exsentences=1&&titles="
     http.Open "GET", sURL & term, False
     http.send

     Dim jsonResponse As Dictionary
     Set jsonResponse = JsonConverter.ParseJson(http.responsetext)
     Debug.Print http.responsetext

    Sheet2.Cells(x, 5).Value = http.responsetext
    x = x + 1
    Loop

The code above actually isn't running at all, so I don't know where to direct my focus. I've tried using "Debug.Print http.responseText" but it's not returning any result so I'm clearly doing something very wrong. Any help appreciated.

at least the URL string described in Post and the URL navigated in code is different. Code URL is only fetching {"batchcomplete":""}. code URL missing title part, may be typo, please have a look. — Ahmed AU

QHarr QHarr · Accepted Answer · 2019-07-18T06:17:27

Sheet2 is a code name not necessarily the visible name so verify correct sheet first.

Add option explicit and declare all variables with their type

Then your url has an additional & at the end. Remove that and it works

Option Explicit
Public Sub test()
    Dim http As Object, term As String, x As Long
    x = 2
    Set http = CreateObject("MSXML2.XMLHTTP")

    Do While Sheet2.Cells(x, 4) <> ""
        term = Sheet2.Cells(x, 4)

        Const sURL As String = "https://en.wikipedia.org/w/api.php?format=json&action=query&prop=extracts&explaintext=&exintro=&exsentences=1&titles="
        http.Open "GET", sURL & term, False
        http.send

        Dim jsonResponse As Dictionary
        Set jsonResponse = JsonConverter.ParseJson(http.responseText)
        Debug.Print http.responseText

        Sheet2.Cells(x, 5).Value = http.responseText
        x = x + 1
    Loop
End Sub

Trying to extract Wikipedia JSON data using Excel VBA

1 Answers