StormNet
asked on
Use VBA to upload JSON data.
Hi there,
I have a project where I need to use a shopping cart's API to update product information from time to time.
I can download the product information, but am unable to devise a way to upload the information I need to change. The code that works for the download is
The shopping cart specifies the following;
Format: JSON (both input and output)
HTTP Method: PUT, Content-type: text/json
I'm using Microsoft Access 2003, Windows 7.
I would really appreciate any assistance.
Regards
I have a project where I need to use a shopping cart's API to update product information from time to time.
I can download the product information, but am unable to devise a way to upload the information I need to change. The code that works for the download is
Sub test_successful_JSON_downl
Dim xhr As Object, webServiceURL as string
webServiceURL = "http://app.ecwid.com/api/v1/9999999/products"
Set xhr = CreateObject("Microsoft.XM
xhr.Open "GET", webServiceURL , False
xhr.Send
If xhr.Status = 200 Then
MsgBox xhr.getAllResponseHeaders
Else
MsgBox xhr.Status & ": " & xhr.StatusText
End If
Set xhr = Nothing
End Sub
Dim xhr As Object, webServiceURL as string
webServiceURL = "http://app.ecwid.com/api/v1/9999999/products"
Set xhr = CreateObject("Microsoft.XM
xhr.Open "GET", webServiceURL , False
xhr.Send
If xhr.Status = 200 Then
MsgBox xhr.getAllResponseHeaders
Else
MsgBox xhr.Status & ": " & xhr.StatusText
End If
Set xhr = Nothing
End Sub
The shopping cart specifies the following;
Format: JSON (both input and output)
HTTP Method: PUT, Content-type: text/json
I'm using Microsoft Access 2003, Windows 7.
I would really appreciate any assistance.
Regards
ASKER
Hi there,
Thanks for coming back to me!
The only information I can find is here http://kb.ecwid.com/w/page/9999999/Product%20API under the subheading "REST API Method: update a product"
with the working download method I get the following json format
[
{
"id": 21884539,
"sku": "00000",
"name": "Test product 1",
"price": 0.0,
"weight": 0.0,
"url": "http://www.missmossies.yolasite.com#!/~/product/id=21884539",
"created": "2013-03-29 09:41:30",
"productClassId": 0,
"enabled": true,
"description": "<p>SIDTIMEKEEPERSCOZA</p>
}
]
I'm working on the assumption that this is the format I need to use for an upload because of the error message I get when I try the code below:
The responsetext is "Error parsing JSON: A JSONObject text must begin with '{' at character 0"
Somewhere I need to add in the command to send the JSONObject... but I dont know the format or the syntax.... *sigh* and I have been searching...
I appreciate any help...
Regards
Thanks for coming back to me!
The only information I can find is here http://kb.ecwid.com/w/page/9999999/Product%20API under the subheading "REST API Method: update a product"
with the working download method I get the following json format
[
{
"id": 21884539,
"sku": "00000",
"name": "Test product 1",
"price": 0.0,
"weight": 0.0,
"url": "http://www.missmossies.yolasite.com#!/~/product/id=21884539",
"created": "2013-03-29 09:41:30",
"productClassId": 0,
"enabled": true,
"description": "<p>SIDTIMEKEEPERSCOZA</p>
}
]
I'm working on the assumption that this is the format I need to use for an upload because of the error message I get when I try the code below:
Dim xhr As Object, thisRequest As String, jsonString As String
thisRequest = "https://app.ecwid.com/api/v1/9999999/product?ID=21884546&secure_auth_key=xyzxyxxyxxyz"
jsonString = "{""name"":""test remote update""}"
Set xhr = CreateObject("Microsoft.XM
xhr.Open "PUT", thisRequest, False
xhr.Send
Debug.Print xhr.responseText
Set xhr = Nothing
thisRequest = "https://app.ecwid.com/api/v1/9999999/product?ID=21884546&secure_auth_key=xyzxyxxyxxyz"
jsonString = "{""name"":""test remote update""}"
Set xhr = CreateObject("Microsoft.XM
xhr.Open "PUT", thisRequest, False
xhr.Send
Debug.Print xhr.responseText
Set xhr = Nothing
The responsetext is "Error parsing JSON: A JSONObject text must begin with '{' at character 0"
Somewhere I need to add in the command to send the JSONObject... but I dont know the format or the syntax.... *sigh* and I have been searching...
I appreciate any help...
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi aikamark,
I'm unfortunately inexperienced in this area, so I may need some really basic assistance.
I had a look at your suggestions and I thought http://www.exceltoolset.com/free-excel-tool-json-parser-for-vba/ would be a good starting point. I opened the spreadsheet but when I tried to run some of the routines I got errors on Debug.assert and the Replace function. I wasn't able to resolve them, despite activating different References.
I then copied the code into an Access module and everything seems to be ok except for the line
Dim lib as new JSONLib
Try as I can I cant find any info on the Reference to activate to allow this to be accepted.
Do you know how to get past this?
Much thanks...
I'm unfortunately inexperienced in this area, so I may need some really basic assistance.
I had a look at your suggestions and I thought http://www.exceltoolset.com/free-excel-tool-json-parser-for-vba/ would be a good starting point. I opened the spreadsheet but when I tried to run some of the routines I got errors on Debug.assert and the Replace function. I wasn't able to resolve them, despite activating different References.
I then copied the code into an Access module and everything seems to be ok except for the line
Dim lib as new JSONLib
Try as I can I cant find any info on the Reference to activate to allow this to be accepted.
Do you know how to get past this?
Much thanks...
In your code window, click on the Tools menu. The first submenu item is references.
When I posted my earlier comment, I thought you were receiving JSON data into Access. Some of those links may only be applicable for consuming JSON, not producing JSON.
When I posted my earlier comment, I thought you were receiving JSON data into Access. Some of those links may only be applicable for consuming JSON, not producing JSON.
ASKER
What reference would I select? I've looked but I cant seem to find a reference including the word 'Json'.
Do you have a specific reference in mind?
Do you have a specific reference in mind?
did you go through the installation instructions after downloading?
ASKER
The only files in the download were a demo excel file and sample json file. I checked all the code in the excel file to see if there were any instructions there but no luck. I checked the download page for anything resembling instructions but no luck as well.
There are no wiki pages either.
Am I missing it?
There are no wiki pages either.
Am I missing it?
JSONLIB is a class module. Have you added that class from the downloaded workbook into your testing workbook code environment?
ASKER
Cool, got it! First time I've ever imported a .cls file into MSAccess!
There are no more compile errors. While I was tinkering I found other related info and after modifying it it now works properly.
Dim xhr As Object, thisRequest As String
Set xhr = CreateObject("Microsoft.XM
thisRequest = "https://app.ecwid.com/api/v1/999999/product?id=21884546&secure_auth_key=xyzabcdefg" 'changed case of ID to id
xhr.Open "PUT", thisRequest, False
xhr.setRequestHeader "Content-type", "text/json" 'inserted this line
xhr.Send "{""id"":21884546,""name""
Debug.Print xhr.Status
Debug.Print xhr.StatusText
Debug.Print xhr.responseText
Set xhr = Nothing
Much thanks to all who got me thinking and moving towards this solution!
There are no more compile errors. While I was tinkering I found other related info and after modifying it it now works properly.
Dim xhr As Object, thisRequest As String
Set xhr = CreateObject("Microsoft.XM
thisRequest = "https://app.ecwid.com/api/v1/999999/product?id=21884546&secure_auth_key=xyzabcdefg" 'changed case of ID to id
xhr.Open "PUT", thisRequest, False
xhr.setRequestHeader "Content-type", "text/json" 'inserted this line
xhr.Send "{""id"":21884546,""name""
Debug.Print xhr.Status
Debug.Print xhr.StatusText
Debug.Print xhr.responseText
Set xhr = Nothing
Much thanks to all who got me thinking and moving towards this solution!
ASKER
While not the exact solution one of the links provided the "bridging direction" that kickstarted a different path.
I am not able to get to the web-site <https://code.google.com/p/vba-json/> to see the code you are referring to. I have been programming in VBA/ACCESS for quite a few yrs but new to JSON and REST. Would it be possible to see a sample of your ACCESS Database to understand how to grab the REST data, using JSON? Thank you very much!!
Or do this in .NET - I'm sure they have examples for that.