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
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