Import from api into multiple excel cells

Question

I have an api which displays a two-dimension array.

Array
(
    [0] => Array
        (
            [0] => 0
            [1] => 1
            [2] => 2
        )

    [1] => Array
        (
            [0] => 3
            [1] => 4
            [2] => 5
        )

)

How can I import the api into excel so that the first number(0) will go to A1. The second number(1) to B1. Something like this

   A  B  C
1| 0  1  2
2| 3  4  5

Answer 1

If you have the array in Excel already it is fairly trivial, just need to make sure your range is the same size as your array, you can do this using the lower boundary and upper boundary of the array like this:

Sub MultiDimension()
Dim MyArr(2, 3) As Long
MyArr(0, 0) = 0
MyArr(0, 1) = 1
MyArr(0, 2) = 2
MyArr(1, 0) = 3
MyArr(1, 1) = 4
MyArr(1, 2) = 5
Range("A1:A1").Resize(UBound(MyArr, LBound(MyArr) + 1), UBound(MyArr, UBound(MyArr))) = MyArr
End Sub

Edit: This will do what you want.

Sub ReadFromAPI()
Dim MyString As String, MyVal As String, D1 As Long, D2 As Long, MyArr() As Variant, X As Long, APIURL As String

APIURL = "http://iqamah.org/api/test.php"
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", APIURL, False
    .Send
    MyString = .ResponseText
End With
If MyString <> "" Then
    D1 = ((Len(MyString) - Len(Replace(MyString, " => Array", ""))) / 9) - 1
    D2 = (Len(MyString) - Len(Replace(MyString, "(", ""))) - 1
    ReDim MyArr(D1, D2)
    For X = LBound(Split(MyString, vbLf)) To UBound(Split(MyString, vbLf))
        MyVal = Split(MyString, vbLf)(X)
        If Replace(MyVal, "=>", "") <> MyVal Then
            If Replace(MyVal, "=> Array", "") <> MyVal Then
                D1 = Mid(MyVal, InStr(1, MyVal, "[") + 1, (InStr(1, MyVal, "]")) - (InStr(1, MyVal, "[") + 1))
            Else
                D2 = Mid(MyVal, InStr(1, MyVal, "[") + 1, InStr(1, MyVal, "]") - (InStr(1, MyVal, "[") + 1))
                MyArr(D1, D2) = Right(MyVal, Len(MyVal) - (InStr(1, MyVal, "=> ")) - 2)
            End If
        End If
    Next
    Range("A1:A1").Resize(D1 + 1, D2 + 1) = MyArr
Else
    MsgBox "Nothing returned, Site might be down", vbOKOnly
End If
End Sub

Code as a worksheet event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyString As String, MyVal As String, D1 As Long, D2 As Long, MyArr() As Variant, X As Long, APIURL As String

If Target = Range("M19") Then
    Application.EnableEvents = False
    APIURL = "http://iqamah.org/api/test.php?id=" & Target.Text
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", APIURL, False
        .Send
        MyString = .ResponseText
    End With
    If MyString <> "" Then
        D1 = ((Len(MyString) - Len(Replace(MyString, " => Array", ""))) / 9) - 1
        D2 = (Len(MyString) - Len(Replace(MyString, "(", ""))) - 1
        ReDim MyArr(D1, D2)
        For X = LBound(Split(MyString, vbLf)) To UBound(Split(MyString, vbLf))
            MyVal = Split(MyString, vbLf)(X)
            If Replace(MyVal, "=>", "") <> MyVal Then
                If Replace(MyVal, "=> Array", "") <> MyVal Then
                    D1 = Mid(MyVal, InStr(1, MyVal, "[") + 1, (InStr(1, MyVal, "]")) - (InStr(1, MyVal, "[") + 1))
                Else
                    D2 = Mid(MyVal, InStr(1, MyVal, "[") + 1, InStr(1, MyVal, "]") - (InStr(1, MyVal, "[") + 1))
                    MyArr(D1, D2) = Right(MyVal, Len(MyVal) - (InStr(1, MyVal, "=> ")) - 2)
                End If
            End If
        Next
        Range("A1:A1").Resize(D1 + 1, D2 + 1) = MyArr
    Else
        MsgBox "Nothing returned, Site might be down", vbOKOnly
    End If
    Application.EnableEvents = True
End If
End Sub

Answer 2

I would recommend exporting the PHP array to a CSV file, should come out like you expect.

Answer 3

The range you are specifying for target-value must contains some number to avoid error...

You should try the following changes, working for me...

In the Procedure Worksheet_Change() change this line of code:

D1 = ((Len(MyString) - Len(Replace(MyString, " => Array", ""))) / 9) - 1

with plus (+) as:

D1 = ((Len(MyString) - Len(Replace(MyString, " => Array", ""))) / 9) + 1

And this

D2 = (Len(MyString) - Len(Replace(MyString, "(", ""))) - 1

with this one:

D2 = (Len(MyString) - Len(Replace(MyString, "(", ""))) + 1

Then use the following procedure to run the code:

Sub runCode()
    Worksheet_Change (Worksheets("sheet1").Range("m19"))
End Sub