Accessing OData API with Excel Macros

The OData API can be used with Excel macros to retrieve data from OLAP, execute Integrator tasks, and interact with endpoints exposed by the OData API. In the example below, an Excel macro with a VBA script runs an Integrator Job via OData.

Note that the example below uses sample parameters and must be customized for your environment.

Copy

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Sub ReadFromAPI()

    ' Set up variables

    Dim objHTTP As Object
    Dim strUrl As String
    Dim strUsername As String
    Dim strPassword As String
  
    ' Set the URL and authentication credentials

    strUrl = "http://odata.jedox.localhost/Integrator('globalprojects')/projects('sampleBiker')/jobs('default')/Run(waitForTermination=true)"
    strUsername = "admin"
    strPassword = "admin"
   
    ' Create the HTTP object

    Set request = CreateObject("MSXML2.XMLHTTP")
   
    ' Open the connection and set the headers

    request.Open "GET", strUrl, False
    request.SetRequestHeader "Authorization", "Basic " & Base64Encode(strUsername & ":" & strPassword)
  
    ' Send the request and get the response

    request.Send
    Debug.Print request.ResponseText
  
    If request.Status <> 200 Then
        MsgBox request.ResponseText
        Exit Sub
    End If
  
    Dim response As Object
    Set response = JsonConverter.ParseJson(request.ResponseText)
   
    'Access data
    Debug.Print response("Id"), response("Errors"), response("Warnings"), response("StartDate"), response("Status"), response("ExecutionType"), response("TraceAvailable")
     Range("A1").Value = "Id"
     Range("B1").Value = response("Id")
   
     Range("A2").Value = "Errors"
     Range("B2").Value = response("Errors")
   
     Range("A3").Value = "Warnings"
     Range("B3").Value = response("Warnings")
    
     Range("A4").Value = "Start Date"
     Range("B4").Value = response("StartDate")
   
     Range("A5").Value = "Status"
     Range("B5").Value = response("Status")
  
     Range("A6").Value = "ExecutionType"
     Range("B6").Value = response("ExecutionType")
   
     Range("A7").Value = "TraceAvailable"
     Range("B7").Value = response("TraceAvailable")
End Sub

Function Base64Encode(ByVal sText As String) As String

    Dim oXML As Object
    Dim oNode As Object

    Set oXML = CreateObject("MSXML2.DOMDocument")
    Set oNode = oXML.createElement("base64")
    oNode.DataType = "bin.base64"
    oNode.nodeTypedValue = Stream_StringToBinary(sText)
    Base64Encode = oNode.Text
    Set oNode = Nothing
    Set oXML = Nothing
End Function

Function Stream_StringToBinary(Text As String) As Variant
    Const adTypeText = 2
    Const adTypeBinary = 1

    Dim BinaryStream As Object
    Set BinaryStream = CreateObject("ADODB.Stream")

    BinaryStream.Charset = "us-ascii"
    BinaryStream.Type = adTypeText
    BinaryStream.Open
    BinaryStream.WriteText Text
    BinaryStream.Position = 0
    BinaryStream.Type = adTypeBinary
    BinaryStream.Read 3 ' Ignore BOM
    Stream_StringToBinary = BinaryStream.Read

End Function