0

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"
    Dim JsonHTTP As New MSXML2.XMLHTTP
    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.

Edit: 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
Improve this question
8
  • Is there any way you can provide a reproducible example? Commented Dec 17, 2019 at 13:54
  • @QHarr I'd be happy to, but wouldn't that require me to create an excel sheet with dummy data? Alternatively, I can just edit the post with the loops that I use to add data to the jFullDictionary? Commented Dec 17, 2019 at 13:56
  • Is the url public? And is that the line which is failing? i.e. .Open "POST", JsonURL, False Commented Dec 17, 2019 at 14:01
  • @QHarr the URL is not public, I am running it from localhost. But that's not the issue. The line that it is failing on is .send JsonString Commented Dec 17, 2019 at 14:05
  • What is the value of JsonString when it fails? Have you checked it is the value you expected and valid json? Commented Dec 17, 2019 at 14:10
Related questions
1
Excel+VBA+JSON+Put Requests
6
Sending JSON object as a POST request
1
Error parsing json excel vba
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.