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
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