BI4 RESTful SDK / Excel : Logon/Logoff Sample

Synopsis: In this series of Blogs, I’ll explore how we can use RESTful Raylight Web Services in Excel with VBA.

Previous Entry: http://scn.sap.com/community/restful-sdk/blog/2016/10/03/bi4-restful-sdk-excel-is-back

It’s the first REST sample to do, like the “Hello World” in java & co. I’ll not explain the workflow and how SDK works, you like see the official documentation or Ted Ueda’s blog for this.

We write a VBA macro refresh() in an Excel document (see the file). The steps of this macro are :

1) Set the variables, get the RESTful url

We define objects, although it’s optional in VBA.

With Excel you can have a configuration worksheet, where you can set informations like servername, login, password.

The RESTful url is like http://servername:6405/biprws, you can get it easily in Excel from servername cell.

    Dim boCMS, boName, boPwd, boURL, boToken  As String
    Dim objHTTP As WinHttp.WinHttpRequest
    Dim objXML As MSXML2.DOMDocument
    Dim oNodeXML As MSXML2.IXMLDOMNode
    boCMS = Sheets("config").Cells(1, 2).Value
    boName = Sheets("config").Cells(2, 2).Value
    boPwd = Sheets("config").Cells(3, 2).Value
    boURL = Sheets("config").Cells(4, 2).Value

2) Get the logon request template

We perform a HTTP request to the RESTful web service of logon, and retrieve the logon request template.

Firstly we have to create a WinHttpRequest object. Then we describe the logon url and the verbe of the HTTP request (GET or POST), and set the headers (Content-type and Accept).

ℹ You can skip this step and elaborate the request template from scratch.

ℹ I use XML format and not JSON. You can manipulate XML with standard dlls in Excel. I’m not sure, but if you want to use JSON format in VBA you have to call external libraries or use JScript ; XML is standard and easier for me.

  Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
  objHTTP.Open "GET", boURL & "/logon/long", False
  objHTTP.SetRequestHeader "Content-type", "application/xml"
  objHTTP.SetRequestHeader "Accept", "application/xml"

Then we call the logon url with Send method. We look at the Status of the HTTP Request. If it’s equal to 200 there’s an error, we show the error message in the body of the response (ResponseText) in console and we stop the macro.

    objHTTP.Send ""
    If objHTTP.Status <> "200" Then
        Debug.Print "erreur"
        Debug.Print objHTTP.ResponseText
        Stop
    End If

3) Change the XML logon template

Ok, we have the XML template in body response of the HTTP request. We can see this in debug console with a command like :

Debug.Print objHTTP.ResponseText

We have (reformatting) :

<attrs xmlns="http://www.sap.com/rws/bip">
  <attr name="userName" type="string"></attr>
  <attr name="password" type="string"></attr>
  <attr name="auth" type="string" possibilities="secEnterprise,secLDAP,secWinAD,secSAPR3">secEnterprise</attr>
</attrs>

We need to set the login and password in this XML.

Office’s VBA provides standard library XML Parser, which offers great and easy manipulations of XML. We’ll use it.

We need to create a XMLDOM object, which contains the XML body (loaded in memory).

    Set objXML = CreateObject("Microsoft.XMLDOM")
    objXML.LoadXML (objHTTP.ResponseText)

Next, we change attributes.

ℹ Not tidy, but the easiest way for me is to use positions of attributes 😈 . Otherwise the library is not easy, we need to loop the attributes and see which attribute has the name “userName” or “password”. Or you can elaborate the XML from scratch …

    objXML.SelectSingleNode("/attrs/attr[0]").Text = boName
    objXML.SelectSingleNode("/attrs/attr[1]").Text = boPwd

We can show in console the XML, with XML property :

    Debug.Print objXML.XML

<attrs xmlns="http://www.sap.com/rws/bip">
  <attr name="userName" type="string">login</attr>
  <attr name="password" type="string">password</attr>
  <attr name="auth" type="string" possibilities="secEnterprise,secLDAP,secWinAD,secSAPR3">secEnterprise</attr>
</attrs>

4) Login and get the token

We call the same REST login url, but in POST with the changed XML. We use the same object objHTTP for this.

    objHTTP.Open "POST", boURL & "/logon/long", False
    objHTTP.SetRequestHeader "Content-type", "application/xml"
    objHTTP.SetRequestHeader "Accept", "application/xml"
    objHTTP.Send (objXML.XML)
    If objHTTP.Status <> "200" Then
        Debug.Print "erreur"
        Debug.Print objHTTP.ResponseText
        Stop
    End If

We pick the token in the header of the response, and show it in the console

    boToken = objHTTP.GetResponseHeader("X-SAP-LogonToken")
    Debug.Print "token=" & boToken

token="servername.mydomain.com:6400@{3&2=24796,U3&2v=servername.mydomain.com:6400,UP&66=60,U3&68=secEnterprise:login,UP&S9=6274,U3&qe=100,U3&vz=RmxklmdkjzlkjlkezzeffudJygO_.tfJDUs,UP}"

5) logoff

We call the logoff url with the token picked previously in the header of the request.

    objHTTP.Open "POST", boURL & "/logoff", False
    objHTTP.SetRequestHeader "Accept", "application/xml"
    objHTTP.SetRequestHeader "X-SAP-LogonToken", boToken
    objHTTP.Send ""
    If objHTTP.Status <> "200" Then
        Debug.Print "erreur logoff : " & objHTTP.Status
        Debug.Print objHTTP.ResponseText
    End If

ℹ Howto test : open the document, set the bi4 servername, login, password in the first sheet, and call the macro refresh(). It’s better with Visual Basic console 🙂

❗ I don’t know why, but the zip files are not allowed 😐 I cheat, rename the file from .txt to .zip and unzip it …

Next step : List universes