on 07-04-2018 4:34 PM
Hello, I would like to know if there is a way to download a BO Webi Report (OpenDocument) via VBA with Office Access or Excel. Right now I can Login to BO Webi and open the desired Webi OpenDocument Report with the following VBA Code:
[1]: https://i.stack.imgur.com/p30uV.png
Private Sub btnReportBO_Click()
WebBrowser.navigate "https://xxxxxx.com/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=FjkfNFtyyg4ABJEBAABnyuNvAFBWgRcs"
End Sub
Private Sub WebBrowser_DocumentComplete(ByVal pDisp As Object, url As Variant)
With WebBrowser
If .Document.title = "OpenDocument" Then
.Document.all("_id0:logon:USERNAME").Value = strUsername
.Document.all("_id0:logon:PASSWORD").Value = strPassword
Do: Loop Until .Document.ReadyState = "complete"
.Document.all("_id0:logon:logonButton").Click
End If
End With
End Sub
After that how can download the table?
[2]: https://i.stack.imgur.com/OVe8k.png
I fail because I don't know how to access the Frame or iFrame containing the table. If I download the HTML source code via right click - View Source to my Desktop then I can Export the table to Access.
How can I Access the Report/Table in the Document Frame via VBA?
Or How can I download the entire source code to an text file?
Hi Lionel,
Could you please share with us the VBA code that you use to export the Webi Report?
thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Unfortunately, the RESTFUL API does not seem to be available.
But finally made it with Microsoft HTML Object Library. Now I’m able to open the Webi OpenDocument Report and to Import the table into Office Access via VBA.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Ayman, thank you so much for your answer. Unfortunately, we can only use SAP BusinessObjects BI Platform 4.2 Support Pack 5 (WebI - Browser) at work.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hallo Lionel,
Use Web Intelligence REST API instead of the OpenDocument.
and here's a Quick and Dirty sample code that you can use as a basis. In this example, I export a report (id=1) from a document (id=7131) as HTML
Option Explicit
Dim sLogonToken As String
Dim sBaseURL As String
Private Sub Worksheet_Activate()
sBaseURL = "http://myHost:6405/biprws"
logon
getDocument
logoff
End Sub
Private Sub logon()
Dim sURL As String, Body As String, sRes As String
Dim oHttp As Object
Dim jsonParsed As Dictionary
Set oHttp = CreateObject("MSXML2.XMLHTTP")
sURL = sBaseURL & "/v1/logon/long"
oHttp.Open "POST", sURL, False
oHttp.setRequestHeader "Content-type", "application/json"
oHttp.setRequestHeader "Accept", "application/json"
Body = "{""password"":""myPassword"",""auth"":secEnterprise,""username"":Administrator}"
oHttp.Send Body
sRes = oHttp.ResponseText
Set jsonParsed = JsonConverter.ParseJson(sRes)
sLogonToken = jsonParsed("logontoken")
Set oHttp = Nothing
End Sub
Private Sub logoff()
Dim sURL As String, sRes As String
Dim oHttp As Object
Dim jsonParsed As Dictionary
Set oHttp = CreateObject("MSXML2.XMLHTTP")
sURL = sBaseURL & "/v1/logoff"
oHttp.Open "POST", sURL, False
oHttp.setRequestHeader "Content-type", "application/json"
oHttp.setRequestHeader "Accept", "application/json"
oHttp.setRequestHeader "x-sap-logontoken", sLogonToken
oHttp.Send
sRes = oHttp.ResponseText
MsgBox sRes & " -- Status: " & oHttp.Status
Set oHttp = Nothing
End Sub
Private Sub getDocument()
Dim sURL, sRes As String
Dim oHttp As Object
Set oHttp = CreateObject("MSXML2.XMLHTTP")
sURL = sBaseURL & "/raylight/v1/documents/7131/reports/1"
oHttp.Open "GET", sURL, False
oHttp.setRequestHeader "Content-type", "text/html"
oHttp.setRequestHeader "Accept", "text/html"
oHttp.setRequestHeader "x-sap-logontoken", sLogonToken
oHttp.Send
sRes = oHttp.ResponseText
Sheets("Document").Cells(1, 1) = sRes
Set oHttp = Nothing
End Sub
for more info, see SAP BusinessObjects RESTful Web Service SDK User Guide for Web Intelligence and the BI Semantic Laye...
Exporting Reports ("Exporting a Report in Listing Mode", "Exporting a Report as a Series of Pages" and "Exporting a Page or Range of Pages of a Report")
Exporting a Report Element
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
98 | |
11 | |
11 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.