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
Hi Stephane,
thanks for sharing this information.
I have used VBA in SAP BI environment mainly the designer and infostore libraries.
We ar changing from 3. to BI 4.2 and I try to find VBA solutions to continue my tasks. The restful ws is a good candidate.
To this post: have you ever tried to use the basic authentication fom VBA?
I get various error messages when using it. The serve is configured well for this type of authentication. (I hope...)
I think my request header syntax is not valid.
Kind regards,
Karoly
Bonjour Stéphane,
J'ai une erreur à l'exécution de la macro et ça ne fonctionne sur aucun des PCs ici (Excel 2016 & Macro activée)
Si tu as une piste je suis preneur ! Merci