1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| Dim JSc As Object
Function jsonEval(jsonTXT$) As Object
If JSc Is Nothing Then
Set JSc = CreateObject("ScriptControl")
JSc.Language = "JScript"
JSc.AddCode "function getKeys(jsonObj) { var keys = []; for (var i in jsonObj) { keys.push(i); } return keys; }"
End If
Set jsonEval = JSc.Eval("(" & jsonTXT & ")")
End Function
Function jsonAllColumns(TXT$)
Dim oKeys As Object, oRoot As Object, C&, D&, L&, R&, S$(), V
Set oRoot = jsonEval(TXT)
Set oKeys = JSc.Run("getKeys", oRoot)
If CallByName(oKeys, 0, VbGet) = "0" Then
L = CallByName(oKeys, "length", VbGet) + 1
S = Split(JSc.Run("getKeys", CallByName(oRoot, 0, VbGet)), ",")
D = UBound(S) + 1
ReDim V(1 To L, 1 To D)
For C = 1 To D: V(1, C) = S(C - 1): Next
On Error Resume Next
For R = 2 To L
Set oKeys = CallByName(oRoot, R - 2, VbGet)
For C = 1 To D
V(R, C) = CallByName(oKeys, V(1, C), VbGet)
Next
Next
jsonAllColumns = V
End If
Set JSc = Nothing: Set oKeys = Nothing: Set oRoot = Nothing
End Function
Sub DemoAllColumns()
Dim F%, S$, VA
S = ThisWorkbook.Path & "\Jeu .json"
If Dir(S) = "" Then Beep: Exit Sub
F = FreeFile
Open S For Input As #F
S = Input(LOF(F), #F)
Close #F
Cells(4).CurrentRegion.Clear
VA = jsonAllColumns(S)
If Not IsArray(VA) Then Beep: Exit Sub
Cells(4).Resize(UBound(VA), UBound(VA, 2)).Value = VA
End Sub |
Partager