Dealing with json

[control1]

Hi All,



With the help of my provider, I have managed to get an API call running on my Access Database. When I run the code, it returns some data as XML/JSON and stores it as a string.

I can display it in a message box with the result like so:



I've been trying to figure out how I just get the actual useful data (credit) to be displayed - "2114.0000" and lose all the bumpf around it - but I'm struggling.

Public Sub getCredit()    Dim result As String
     Dim uid As String
     uid = "uid=eventcontrol"
     Dim pass As String
     pass = "&pass=HIDDEN"
     Dim format As String
     format = "&format=json"
     
     Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
     Request.Open "POST", "https://www.voodoosms.com/vapi/server/getCredit?" + uid + pass + format
     Request.Send
     
     result = Request.ResponseText
 
     MsgBox (result)


End Sub

Really appreciate a push in the right direction

Cheers,
Leigh

[CJ_London]

use the data import wizard to import the xml file to a temporary table.

Or if this is always just one value, you can find it with string functions - something like

it looks like your result is 2 lines so you need to go to the 2nd line

dim strA() as string
strA=split(result,chr(10)) ' check what the linefeed character is, might be something other than chr(10)
if unbound(strA)=1 then iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1), instr(strA(1)"<Credit>")+8))

[control1]

use the data import wizard to import the xml file to a temporary table.

Or if this is always just one value, you can find it with string functions - something like

it looks like your result is 2 lines so you need to go to the 2nd line

dim strA() as string
strA=split(result,chr(10)) ' check what the linefeed character is, might be something other than chr(10)
if unbound(strA)=1 then iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1), instr(strA(1)"<Credit>")+8))

Thanks for your reply and help Ajax. I'm still fairly new to VBA - and I'm struggling to understand/get to work that code.

I basically only want to return what's inside the <credit></credit> tags.

Cheers,
Leigh

[CJ_London]

I basically only want to return what's inside the <credit></credit> tags.

your post is not clear about what you want do - return to where? a control? as a function? to a variable to be used elsewhere? Also, your example appears to only return one value. If it returns more you will require a different solution. And you have not clarified what the linefeed character is, which will affect the split function if it is not chr(10)


Assuming a single value returned and linefeed is chr(10) then add this code between the 'result = Request.ResponseText' and 'MsgBox' lines

dim strA() as string
 strA=split(result,chr(10)) ' check what the linefeed character is, might be something other than chr(10)
 if unbound(strA)=1 then msgbox iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1),  instr(strA(1)"<Credit>")+8)))

you should at least see what is being returned

to determine the linefeed character, put this bit of code between the 'result = Request.ResponseText' and 'MsgBox' lines

dim I as integer
for I=1 to len(result)
    debug.print mid(result,i,1) asc(mid(result,i,1))
next i

then inspect in the immediate window - you will be looking for ? followed by > and require the next character(s) until you see < followed by x, then m etc

[control1]

your post is not clear about what you want do - return to where? a control? as a function? to a variable to be used elsewhere? Also, your example appears to only return one value. If it returns more you will require a different solution. And you have not clarified what the linefeed character is, which will affect the split function if it is not chr(10)


Assuming a single value returned and linefeed is chr(10) then add this code between the 'result = Request.ResponseText' and 'MsgBox' lines

dim strA() as string
 strA=split(result,chr(10)) ' check what the linefeed character is, might be something other than chr(10)
 if unbound(strA)=1 then msgbox iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1),  instr(strA(1)"<Credit>")+8))

you should at least see what is being returned

to determine the linefeed character, put this bit of code between the 'result = Request.ResponseText' and 'MsgBox' lines

dim I as integer
for I=1 to len(result)
    debug.print mid(result,i,1) asc(mid(result,i,1))
next i

then inspect in the immediate window - you will be looking for ? followed by > and require the next character(s) until you see < followed by x, then m etc

Thanks again for your reply. Basically the "result" that is being returned via the API call will be a number, that I want to use as a string. So when a user clicks on a button in my form, the api recieves the data, and displays it to the user in the message box. This is working now, but with all of the <xml> coding around it. It will always just be one number.


I've tried the code you suggested:

dim strA() as string strA=split(result,chr(10)) ' check what the linefeed character is, might be something other than chr(10) if unbound(strA)=1 then msgbox iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1), instr(strA(1)"<Credit>")+8))

but I just get an error:



Thanks in advance
Leigh

[control1]

The output from the Immediate window was:

< 60 x 120 
m 109 
l 108 
> 62 
< 60 
r 114 
e 101 
s 115 
u 117 
l 108 
t 116 
> 62 
2 50 
0 48 
0 48 
  32 
O 79 
K 75 
< 60 
/ 47 
r 114 
e 101 
s 115 
u 117 
l 108 
t 116 
> 62 
< 60 
c 99 
r 114 
e 101 
d 100 
i 105 
t 116 
> 62 
2 50 
0 48 
9 57 
0 48 
. 46 
0 48 
0 48 
0 48 
0 48 
< 60 
/ 47 
c 99 
r 114 
e 101 
d 100 
i 105 
t 116 
> 62 
< 60 
/ 47 
x 120 
m 109 
l 108 
> 62 


 10

[orange]

Further to ajax's comments and questions, can you tell us in plain English what you are trying to do?
No JSON, no jargon just plain English.

Can you show us the url and some sample data?

We often see posters who are trying to parse orders/Invoices consisting of several lines. Can you give some idea of the kind of response you'll receive, and what exactly from that response do you need to extract?

There are JSON parsers for vba but I haven't found 1 to work consistently.

Running your code gives

  : result : "<?xml version="1.0" encoding="utf-8"?>
<xml><result>400</result><resultText>BAD REQUEST</resultText></xml>
" : String

Good luck.

[Micron]

Seeing as you have a value for a string variable ("result") how about

Function TestEval()

Dim result As String
Dim startPos As Integer, endPos As Integer

result = "<xml><result>200 OK</result><credit>2114.0000</credit></xml>"
startPos = InStr(1, result, "<credit>", 2) + 8
endPos = InStr(1, result, "</credit>", 2)
MsgBox Mid(result, startPos, endPos - startPos - 1)

End Function

[orange]

Micron,
I agree if he uses the XML response. I don't think he needs or uses JSON format.

[CJ_London]

1. re the result - this is just showing the 2nd line so does not give the linefeed characters - but if this is the full string then all you need to do is change strA(1) to result in this line - also not the spelling correction

if ubound(strA)=1 then msgbox iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1), instr(strA(1)"<Credit>")+8)))

2. the code you have used is missing a bracket at the end - the error message would have told you you were missing a bracket - I corrected it in my post

[control1]

Hi All,
Many thanks for your messages. I'm sorry - I wasn't trying to be awkward. As you can tell, I'm very much a beginner - and just trying to blag my way through VBA.

In plain English, I was just trying to get the number that's returned when the request is returned to be displayed - without all of the other XML codings around it. The code that @micron has provided has achieved this. However, if the returned value drops into the hundreds, it will only be 7 digits long and not work.

I just thought there may have been some code that would recognise anything inbetween two defined <>'s - so even if it was <message>hello world</message) that was returned - there would be something that could pull out just "hello world".

Ajax, I'm sorry that I still can't get the code you have provided to work - I'm still getting error messages. I don't know what I'm looking at really - I've never worked with this type of VBA code before.


Maybe I'm too much of a novice to be attempting this - but I thank you for your help

Leigh

[CJ_London]

Ajax, I'm sorry that I still can't get the code you have provided to work - I'm still getting error messages

That is akin to you ringing the garage and saying 'my car won't work and I can't figure it out'.

suggest post the code you are actually using together with the error messages. They may not mean anything to you at this stage of your knowledge, but will to the people who are trying to help. Also confirm the code you used to generate the list of characters - either you did not use the code I provided

dim I as integer
for I=1 to len(result)
    debug.print mid(result,i,1) asc(mid(result,i,1))
next i

or the result value you got from the response text was structured differently to that provided originally, or you didn't post the entire output.

[Micron]

Hi All,

However, if the returned value drops into the hundreds, it will only be 7 digits long and not work.
I just thought there may have been some code that would recognise anything inbetween two defined <>'s - so even if it was <message>hello world</message) that was returned - there would be something that could pull out just "hello world".
Leigh

This is exactly how it should work. However, it seems to need a correction.
Mid(result, startPos, (endPos - startPos))
not
Mid(result, startPos, endPos - startPos - 1)

[control1]

@Ajax

The code that I have used to generate the list of characters is:

Public Sub getCredit()
    Dim result As String
     Dim uid As String
     uid = "uid=eventcontrol"
     Dim pass As String
     pass = "&pass=hidden"
     Dim format As String
     format = "&format=json"
     
     Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
     Request.Open "POST", "https://www.voodoosms.com/vapi/server/getCredit?" + uid + pass + format
     Request.Send
     
     result = Request.ResponseText


Dim I As Integer
For I = 1 To Len(result)
    Debug.Print Mid(result, I, 1); Asc(Mid(result, I, 1))
Next I


     MsgBox (result)




End Sub

Which returns (in full)

< 60 
? 63 
x 120 
m 109 
l 108 
  32 
v 118 
e 101 
r 114 
s 115 
i 105 
o 111 
n 110 
= 61 
" 34 
1 49 
. 46 
0 48 
" 34 
  32 
e 101 
n 110 
c 99 
o 111 
d 100 
i 105 
n 110 
g 103 
= 61 
" 34 
u 117 
t 116 
f 102 
- 45 
8 56 
" 34 
? 63 
> 62 


 10 
< 60 
x 120 
m 109 
l 108 
> 62 
< 60 
r 114 
e 101 
s 115 
u 117 
l 108 
t 116 
> 62 
2 50 
0 48 
0 48 
  32 
O 79 
K 75 
< 60 
/ 47 
r 114 
e 101 
s 115 
u 117 
l 108 
t 116 
> 62 
< 60 
c 99 
r 114 
e 101 
d 100 
i 105 
t 116 
> 62 
2 50 
0 48 
8 56 
7 55 
. 46 
0 48 
0 48 
0 48 
0 48 
< 60 
/ 47 
c 99 
r 114 
e 101 
d 100 
i 105 
t 116 
> 62 
< 60 
/ 47 
x 120 
m 109 
l 108 
> 62 


 10

The problem that I was having was with this line of code that you told me to try

if ubound(strA)=1 then msgbox iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1), instr(strA(1)"<Credit>")+8)))

Full code that I am entering:

Public Sub getCredit()
    Dim result As String
     Dim uid As String
     uid = "uid=eventcontrol"
     Dim pass As String
     pass = "&pass=hidden"
     Dim format As String
     format = "&format=json"
     
     Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
     Request.Open "POST", "https://www.voodoosms.com/vapi/server/getCredit?" + uid + pass + format
     Request.Send
     
     result = Request.ResponseText


Dim strA() As String
 strA = Split(result, Chr(10)) ' check what the linefeed character is, might be something other than chr(10)
if ubound(strA)=1 then msgbox iif(instr(strA(1),"<Credit>")=0,0,val(mid(strA(1), instr(strA(1)"<Credit>")+8)))






     MsgBox (result)




End Sub

Which returns a Syntax error.

Many thanks,

Leigh

[control1]

Hi Micron,
Many thanks for your help.

So that the code that you provided does work - unfortunately I can't test to see if it still works once the returned value is hundreds rather than thousands, as it's returning the credit balance of my account - so I can't adjust it.

There are other API responses that I could explore, which instead of being <credit></credit> there could be different things - for example:

Public Sub Send()
    Dim result As String
     Dim uid As String
     uid = "uid=eventcontrol"
     Dim pass As String
     pass = "&pass=hidden"
     Dim validity As String
     validity = "&validity=1"
     Dim orig As String
     orig = "&orig=Test"
     Dim msg As String
     msg = "&msg=MESSAGE"
     Dim dest As String
     dest = "&dest=44700000000"
     Dim format As String
     format = "&format=json"
     
     Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
     Request.Open "POST", "https://www.voodoosms.com/vapi/server/sendsms?" + uid + pass + orig + validity + msg + dest + format
     Request.Send
     
     
          result = Request.ResponseText
    


     MsgBox Request.ResponseText
End Sub

Returns:



If i knew a little more about the code - could I doctor it for example to provide me with the value inbetween <resultText>""</resulttext> ?


I feel that I need to go some reading around it so I can try and understand what the code is that you are kindly creating for me - so I can do it myself! Is there a particular name for these references, that I could google?

Best,
Leigh