(Solved) – Issue with sending JSON data from VBA

  • by

I am trying to create an Excel macro to send cell data to a URL using a POST request. I have chosen to format that data using JSON, because there is a lot of it to send. Here is my code for sending the JSON:

    Dim jFullDictionary As New Dictionary ' This dictionary contains a Collection
    Dim JsonURL As String
    JsonURL = "http://myurl.com"
    Set JsonHTTP = CreateObject("MSXML2.XMLHTTP")
    Dim JsonString As String
    JsonString = JsonConverter.ConvertToJson(jFullDictionary, Whitespace:=3)
    With JsonHTTP
        .Open "POST", JsonURL, False
        .setRequestHeader "Content-type", "application/json"
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        .send JsonString ' This is where the program breaks
    End With

Every time I run this, I get the error that says (something along the lines of)

Run time error: Could not find the resource specified

I have already looked at these StackOverflow threads, but I’ve gotten nowhere:
Error from VB excel macro code – msxml3.dll -2146697211 The system cannot locate the resource specified

Sending JSON POST request in VBA

Additionally, I have tried installing Tim Hall’s project from here, but the installation doesn’t work on my machine (might be because it’s a work computer).

The error makes me think that it has something to do with the resources that I have loaded, but I’m not sure. If it’s not that, then there is something wrong with my code. Any help would be appreciated, thank you.

Here is the loop that I use to fill the jFullDictionary with my data:

    Dim jDictionary As New Dictionary
    Dim jCollection As New Collection
    Dim jFullDictionary As New Dictionary

    For i = 1 To excelRange.Rows.Count
        For j = 1 To excelRange.Columns.Count
            jDictionary("row") = i
            jDictionary("name") = Cells(5, j) ' This assignment is arbitrary
            jDictionary("value") = Cells(i   1, j) ' so is this
            jCollection.Add jDictionary
            Set jDictionary = Nothing
        Next j
    Next i

    jFullDictionary.Add "parametersList", jCollection

Leave a Reply

Your email address will not be published. Required fields are marked *