[X]My Assistant

[andywal6]

Hi Guys
I have been using the code below to tease out the calendar ID and Name from a long Jason string for the last five years without problem.
This week, I had problems with MS Office and had to do a full repair.
Since then this code breaks at the line that says. strID = Left(strInput, InStr(1, strInput, ",") - 1)

I have checked it on lots of the databases I have done which use the same code and they all break at the same place.

This is an example of the Jason string from the Debug.Print line

StrInput = {"subcalendars":[{"id":189213,"name":"Andy Walker","active":true,"color":17,"overlap":true,"readonly":false,"creation_dt":"2014-04-09T11:58:48+00:00",

CODE

Private Sub Form_Open(Cancel As Integer)
       'Debug.Print Me.OpenArgs
   PopulateListbox (Me.OpenArgs)

End Sub


Private Sub PopulateListbox(ByVal strInput As String)

'      'Debug.Print " StrInput = " & strInput        *** The Jason string is definitely in strInput ***
    
    Dim strID As String, strName As String, strCalKey As String
    strCalKey = Forms!TeamupDetails.TeamupKey

'       'Clear the listbox of any pre-existing values

    While Me.List18.ListCount > 0
        Me.List18.RemoveItem 0
    Wend

'       'Loop through the JSON data.
    
    While InStr(1, strInput, "id" & Chr(34) & ":") > 0

'       'Look for id": and grab the value immediately
'       'after that, up till the point where you see a comma.

        strInput = Mid(strInput, InStr(1, strInput, "id" & Chr(34) & ":") + 4)
        strID = Left(strInput, InStr(1, strInput, ",") - 1)

'       'Skip over "name":"

        strInput = Mid(strInput, InStr(1, strInput, ",") + 9)     '*** Breaks here ***

'       'Grab the name data up until the next quotation mark

        strName = Left(strInput, InStr(1, strInput, Chr(34)) - 1)
        Me.Text22 = strCalKey
        Me.List18.AddItem strID & "; " & strName
        
    Wend

End Sub

Everything looks ok to me but it breaks with, Run-time error '5': Invalid procedure call or argument.

Any thoughts anyone, or a better way of teasing them out.

Many thanks for viewing.
Andy

[andywal6]

Problem Solved.

On inspection of the Jason string I found that TeamUp Calendar people in their infinite wisdom added a few more, ' id": ' strings into their Jason which were being picked up by my InStr() count.

I have changed the line to find, {"id": instead of id":

strInput = Mid(strInput, InStr(1, strInput, "{" & Chr(34) & "id" & Chr(34) & ":") + 6)

And it all works, for now.

This is what happens if you use non standard Jason Parsing.

Now I'll just wait for them to move the ID and Name to a different place on the string.

Thank you for viewing.

Andy.......

[isladogs]

Just in case you're interested, see JSON Analyse & Transform for Access

[andywal6]

Thank you Colin, I will.
Andy.....