How can we pilot business object by Excel VBA ?

[Former Member]

Hello everyone !

The titlle is pretty explicit but to completing in few words my need, I would like to pilot Busines Object XI (or deski) by a macro excel (VBA). More precisely, I need to :

-open a request

-refresh the datas

-extract the results in another excel document

Anyone has a code which allow this things ?

Thanks a lot in advance

Regards,

Clément

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Bonjour à tous,

Même si le titre est assez explicite, je vais compléter ma demande. J'aimerai depuis une macro excel pouvoir piloter l'apllication BO XI. Plus précisement, j'aimerai pouvoir :

-ouvrir une requête

-la rafrachir

-extraire les données

Quelqu'un connait-il un code VBA permettant cela ?

D'avance, merci beaucoup

Cordialement,

[former_member411491 · Accepted solution]

Hi

hope this will helpfull to you

'Get logon info for BOXI

    lstrUser = GetPassword("XXXX")

    lstrPass = GetPassword("XXXX")

    lstrPass = IIf(UCase$(lstrPass) = "NULL", "", lstrPass)

    lstrCMS = GetPassword("XXXX")

    'Create a Business Objects session and log in to it.

    AppXLDisplayAlerts = appXL.DisplayAlerts

    appXL.DisplayAlerts = False

    Set appBO = CreateObject("BusinessObjects.Application.11")

    appBO.Interactive = False

    StatMsg "Log on to BO session..."

    On Error GoTo bo_refresh_fail

    appBO.Logon lstrUser, lstrPass, lstrCMS, "secEnterprise", 0, 0

    appBO.Interactive = True

    appBO.Visible = True

    'appBO.Window.State = boMinimized

    '**Refresh the  report and save as Excel

    'Open the report which is kept in the same directory as this code filled treat of a workbook; making sure that the main tab is active

    StatMsg "Open report abc.rep..."

    Set docBO = appBO.Documents.Open(lstrLocation & "abc.rep")

    docBO.Reports("Summary Report").Activate

    'Now create the required parameters for the refresh

    docBO.Variables.Item("Period").Value = XXX

    docBO.Variables.Item("Project Code (%)").Value = XXX

    'Now  refresh

    StatMsg "Refresh report abc.rep..."

    appBO.Interactive = False

    docBO.Refresh

[Former Member · Accepted solution]

Thank you so much Sanjay !

It's good, easy and very helpfull...just perfect !

Have a very nice day

[former_member411491]

Hi

am also use the same technique.you have to create a variable of busobj.Application. That create an object for Business Object .

Dim appBO As busobj.Application

Set appBO = CreateObject("BusinessObjects.Application.11")

appBO.Logon with desire username and password

appBO.Interactive = True

appBO.Visible = True

Set docBO = appBO.Documents.Open(lLocation & "Name.rep")

    docBO.Reports("Tab").Activate

Now create the required parameters for the refresh

    docBO.Variables.Item("Period").Value = value

    docBO.Variables.Item("Project Code (%)").Value = value

docBO.Refresh

    'docBO.Save

docBO.SaveAs lstrOutputFile

    appBO.Interactive = True

    docBO.Close (False)

Hope this will helpful to you

[Former Member]

Hi Sanjay

Thank you so much for your quick response !

However, I still have problem with the third line.

Indeed, for every tests I do, I always have a same problem message : "Logon failed" (err '306')

Shall you help me again ? Can you describe with more precision this line ?

Dim appBO As busobj.Application

Set appBO = CreateObject("BusinessObjects.Application.12")

appBO.Logon "CYEC5486", "My_Password1", "@WIXDEV2(J2EE Portal)", "Enterprise", False, False

In the tickboxe (or check boxes), I selected :

/!\ : ERRATUM on in my fist post, I work on BO.12 and not BO.11

Thanks in advance,

Clément