?
Solved

Use VBA to upload JSON data.

Posted on 2013-06-04
13
Medium Priority
?
6,077 Views
Last Modified: 2016-10-27
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

Sub test_successful_JSON_download()

 Dim xhr As Object, webServiceURL  as string
 
 webServiceURL = "http://app.ecwid.com/api/v1/9999999/products"
 Set xhr = CreateObject("Microsoft.XMLHTTP")
 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
0
Comment
Question by:StormNet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 85
ID: 39219674
Does the API provide any examples for Visual Basic (not .NET, but standard VB)? If not, the only way to really determine the exact syntax needed would be to contact the site and ask them for this.

Or do this in .NET - I'm sure they have examples for that.
0
 

Author Comment

by:StormNet
ID: 39219882
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>\n<p>CASIO</p>\n<p>EDIFICE</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.XMLHTTP")
    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
0
 
LVL 46

Accepted Solution

by:
aikimark earned 1500 total points
ID: 39222448
have you looked at any JSON objects or libraries, such as VBJSON?
http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html
https://code.google.com/p/vba-json/
http://www.exceltoolset.com/free-excel-tool-json-parser-for-vba/

You might also be able to use the scriptcontrol library, specifying jscript instead of vbscript as the language.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:StormNet
ID: 39226585
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...
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39226673
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.
0
 

Author Comment

by:StormNet
ID: 39226963
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?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39227094
did you go through the installation instructions after downloading?
0
 

Author Comment

by:StormNet
ID: 39228147
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?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39229981
JSONLIB is a class module.  Have you added that class from the downloaded workbook into your testing workbook code environment?
0
 

Author Comment

by:StormNet
ID: 39230446
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.XMLHTTP")
    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"":""remotely updated 2""}"    
'inserted id into json string
   
   
    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!
0
 

Author Closing Comment

by:StormNet
ID: 39230463
While not the exact solution one of the links provided the "bridging direction" that kickstarted a different path.
0
 

Expert Comment

by:vfinato
ID: 40274013
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!!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question