BO BI 4.2 SP5 Webi - OpenDocument Report download via VBA

[Former Member]

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?

[former_member839989]

Hi Lionel,

Could you please share with us the VBA code that you use to export the Webi Report?

thanks

[Former Member]

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.

[Former Member]

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.

[ayman_salem]

What I understand from your question is that you want to use VBA in Excel or MS Access to download WebI documents / reports or parts of a report. and the included sample VBA code does that.

[ayman_salem]

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