BusinessObjects Board

BO BI 4.2 SP5 Webi - OpenDocument Report download via VBA

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:

    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?
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 with the following Code:

    With WebBrowser
        If .Document.title = "" Then
            Dim x As Long
            Dim RS As DAO.Recordset
            Set RS = CurrentDb.OpenRecordset("tbl_ReportBO")
            If .Document.getElementsByTagName("table")(0).Rows(0).Cells(0).innerText = "Route" Then
                With .Document.getElementsByTagName("table")(0)
                    For x = 1 To .Document.getElementsByTagName("TABLE")(0).Rows.Length - 1
                        RS.AddNew
                            RS("Route") = .Rows(x).Cells(0).innerText
                        RS.Update
                    Next x
                End With
            End If
        End If
    End With

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?

Best regards


Esilance (BOB member since 2018-07-04)

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.


Esilance (BOB member since 2018-07-04)

Hi there i see you were able to download the table using VBA.

I have a similar issue instead of Microsoft HTML. I have a bunch of OpenDoc urls which I want to download or save as PDF copy using Enterprise account.

Appreciate if you could share your thoughts.

Kash

If you use the sOutputFormat=P parameter in the OpenDocument URL, you will get the report output as a PDF file.

-Dell