BusinessObjects Board

Fill LiveOffice prompt with VBA (vb.NET)

We have several report templates, word documents, that contain linked webi documents.
I did already some research, and I am able to refresh the document with external code (thank to the sample I found on this forum), but I didn’t find anything for populating prompt values (eg Customer) without user interaction.

Does anyone have such example?

Thank you,

vilmarci :netherlands: (BOB member since 2006-10-04)

Hm, no replies so far. Is this, because it is impossible, or am I the only one with such a problem? :slight_smile:

vilmarci :netherlands: (BOB member since 2006-10-04)

Hi Marton,

I’m actually struggling on the same subject : how to fill in the prompt to automatically update a Live Office report.

I was thinking of using VBA to identify the Live Office form window, and once found, using Sendkeys to simulate the user input.

So, first step : identify the Live Office prompt window to bring it in foreground. I just checked with WindowSpy :

So, the Live Office prompt window :

  • is always named “Live Office” (easy, yeah ! :mrgreen: )
  • the class is something like “”
  • the window is a child of the desktop window (parent handle=0), not a child of the Live Office document

With these informations, I was able to write a part of VBA code to find the handle of the Live Office prompt window. So the steps should be something like :

  • connect to SAP BO using the CrytalAddin.LogOn method
  • refresh the Live Office objects using CrytalAddin.LiveObjects.Refresh (True) (true to display the prompt window)
  • identify the prompt window, and once it’s done, set it to foreground, and use Sendkeys to complete the prompt and submit for refreshing the data

But it doesn’t work : just after the CrytalAddin.LiveObjects.Refresh (True) instruction, the Live Office document open the prompt window, but this form is modal. As a result, the VBA code is stopped, waiting for a user action to close the modal window. So, I’ve got the code to identify the prompt window, but I’m unable to use it once the window appears…

djikstra :fr: (BOB member since 2004-03-23)

Hi djikstra,
Meanwhile I also went to this direction, as I haven’t found any kind of documentation so far.
The workaround was a multi-threading application:
Thread one is controlling the refresh process, while in the background another process is waiting for the LiveOffice window, and when found, fills the prompts with pre-recorded sendkey sequences and “clicks” the OK button.
From this point, the original thread is taking over the work again.

There are a couple of imitations tough:
multi-threading does not work with VBA (as far as I know)
if you want to schedule this solution, you need a user logged on all the time, disable screen saver (screen lock) and store the credentials.

I admit, it is not nice, but at least it works :slight_smile:


vilmarci :netherlands: (BOB member since 2006-10-04)

OK I have something new : it works :mrgreen:

Well, at least it works for me : I want to fill in the unique prompt of my Word document, and this document is using several LiveOffice objects created thanks to a WebI doc.

The sample code (here) in the forum suggested to load the dll file. Once done, you are able to use the CrystalAddin class in your VBA code.
But that’s not enough : you also have to add the webi_module 1.0 Type Library in your project references

Now, you’re ready to code : in my Word document, I created a new class module named loClass, with the following code :

Public WithEvents loEvent As CrystalAddin

Private Sub loEvent_AfterDocumentLoad(ByVal doc As CRYSTAL_ADDIN_FRAMEWORKLib.IDocument)
    Dim loObj, loPrompt, loPromptValues As Object
    ' Update prompt value for each LiveOffice object
    Dim i As Integer
    For i = 1 To loEvent.LiveObjects.Count
        Set loObj = loEvent.LiveObjects.Item(i - 1)
        Set loPrompt = loObj.WebiAddinPrompts.GetItem(0)
        Set loPromptValues = loPrompt.CurrentValues.GetItem(0)
        ' set your own value using Value property
        loPromptValues.Value = "Enter_the_value_here"
End Sub

Then I added this code in ThisDocument :

Option Explicit
Dim lo As New loClass

Private Sub Document_Close()
    Set lo.loEvent = Nothing
End Sub

Private Sub Document_Open()
    Set lo.loEvent = Application.COMAddIns("CrystalOfficeAddins.CrystalComAddin.7").Object
End Sub

And that’s all : now my LiveOffice objects are updated with the specified value just after the loading of the doc

I hope this could help !

djikstra :fr: (BOB member since 2004-03-23)

Wow, I’ll try. How did you discover this? Is there any documentation for this?

vilmarci :netherlands: (BOB member since 2006-10-04)

No documentation about this, and that point was confirmed by SAP, unfortunately…

So I just played during several hours with the VBA debug mode, trying to spy what was returned by the LiveObjects.Item part of the code, and which property or function could do the job.

That’s how I found the WebiAddinPrompts information. But this class wasn’t available in the crystal_addin library. So I just loaded all the libraries that sound linked to WebI, and used the VBA Object Browser (just type F2 to launch it) to search for the class WebiAddinPrompts.

Once identified in the webi_module library, the solution was almost complete

djikstra :fr: (BOB member since 2004-03-23)

Thank you, it was working perfectly :slight_smile:

vilmarci :netherlands: (BOB member since 2006-10-04)

Maybe one more question to this topic.
I have reports that have multiple prompts. One of them is optional.
So, the code fails here due to null exception:

loPromptValues = loPrompt.CurrentValues.GetItem(0)

This indeed looks logical, as the prompt has no values set, so I guess, I need to add somehow this.

So, I added this:

If IsNothing(loPromptValues) Then
      loPromptValues() = loPrompt.CurrentValues.Add(strCurrPromptValue)
      loPromptValues.Value = strCurrPromptValue
End If

to my code. However this does not work, the .Add method requires an object. So I think, I need to create an object first, fill the values and then add it to the CurrentValues collection. Could you please help me out with this?

Thank you,

vilmarci :netherlands: (BOB member since 2006-10-04)

Hi Marton,

I just had a look to the loPrompt object, and I see some interesting properties :

  • loPrompt.isOptional is a boolean that could be useful for your tests
  • loPrompt.DefaultValues.GetItem(0) seems to be empty on my project (I don’t use optional prompt) but it could be the place where the default value of an optional prompt is stored ?

Maybe you should try this ?

djikstra :fr: (BOB member since 2004-03-23)

I cannot really do much with either the CurrentValues or the Defaultvalues objects.
Based on the object model both have a type WebiAddinValues.

So, as far as I understand, it goes like this:

WebiAddinValues (object)
----WebiAddinValue (object)

Also a little confused with your code:

loPromptValues.Value = "Enter_the_value_here" 

Indeed, it is working, and during a debug of a normal prompt, I can see the “Value” property, but as above, the WebiAddinValue object has no such property in the object model. So, I am wondering, what kind of object it really is.

so, I ended up with this code:

Relevant declarations part

        Dim loPrompt As Object
        Dim loPromptValues As IWebiAddinValue
        Dim loObj As Object
        Dim NewValue As IWebiAddinValue
        Dim NewValues As IWebiAddinValues


                For j = 0 To strPromptArr.Length - 1
                    strCurrPromptValue = strPromptArr(j)
                    loPrompt = loObj.WebiAddinPrompts.GetItem(j)
                    loPromptValues = loPrompt.CurrentValues.GetItem(0)
                    If IsNothing(loPromptValues) Then

                        NewValues = New webi_moduleLib.WebiAddinValues
                        NewValue = New webi_moduleLib.WebiAddinValue

                        NewValue.ValueDataType = WebiAddinPromptType.WEBIADDIN_PROMPT_TYPE_TEXT
                        loPrompt.addvalues(NewValues, False)
                        loPrompt.addvalue(NewValue, False)

                        loPromptValues = loPrompt.CurrentValues.GetItem(0)
                        loPromptValues.ValueDataType = 0
                        loPromptValues.ValueString = strCurrPromptValue
                        loPromptValues.ValueString = strCurrPromptValue
                    End If

The strPromptArr variable contains my prompts as an array. When I declared the loPromptValues object, I had to replace the loPromptValues.Value property part with loPromptValues.ValueString because, the build failed with “non-member” error. I don’t see any difference when whether I put True or False in the AddValue(s) part.
The first parameter is mandatory, with an existing value, the second in my report is optional, with no value. This is processed in the first part. The code fails at the very last row, when I try to add value to the prompt, saying that “The method or operation is not implemented.”

Attached the 2 screen shots how different is the 2 object, even tough, they should look the same… Maybe the missing properties are implemented from another object? Is there any chance that someone from SAP would provide an official code snippet on this?

vilmarci :netherlands: (BOB member since 2006-10-04)


While running my code in debug mode, I see my loPromptValues object inherits from class WebiAddinDiscretePromptValue, not from the class WebiAddinValues you’re using. And in this class WebiAddinDiscretePromptValue, I can see the property Value that I’m using.

Edited : Can you maybe try the following code ?
Declaration part

Dim loObj As WebiAddinDocument
Dim loWebiPrompt As WebiAddinPrompt
Dim loWebiPromptValue As WebiAddinDiscretePromptValue

Once loObj exists, use it like this :

Set loWebiPrompt = loObj.WebiAddinPrompts.Item(0)
If (loWebiPrompt.isOptional) Then
	Set loWebiPromptValue = New WebiAddinDiscretePromptValue
	loWebiPromptValue.Value = "enter_value_here"
	loWebiPrompt.AddValue loWebiPromptValue, True
End If

I don’t really know what’s the meaning of the boolean parameter in loWebiPrompt.AddValue, and I admit I didn’t made several tries to understand what it is.

djikstra :fr: (BOB member since 2004-03-23)

Yesss, the WebiAddinDiscretePromptValue object was the key :slight_smile:
I finished my script and also solved another problem of adding multiple values per prompt.
I put the final app for review, so everyone can download if needed / interested.
Hereby the relevant code:

            LoCount = lo.LiveObjects.Count

            For i = 0 To LoCount - 1
                loObj = lo.LiveObjects.Item(i)

                For j = 0 To strPromptArr.Length - 1
                    strCurrPromptValue = strPromptArr(j)
                    loPrompt = loObj.WebiAddinPrompts.GetItem(j)

                    'Split prompt value string
                    If String2Array(strCurrPromptValue, ";", 2) Then

                        For x = 0 To strPromptValueArr.Length - 1 'walk trough all prompts
                            loPromptValues = loPrompt.CurrentValues.GetItem(x)

                            If IsNothing(loPromptValues) Then
                                NewValue = New webi_moduleLib.WebiAddinDiscretePromptValue
                                NewValue.ValueDataType = WebiAddinPromptType.WEBIADDIN_PROMPT_TYPE_TEXT
                                NewValue.Value = strPromptValueArr(x)
                                loPrompt.addvalue(NewValue, False)
                                loPromptValues = loPrompt.CurrentValues.GetItem(x)
                                'loPromptValues.ValueDataType = 0
                                '                        loPromptValues.Value = strCurrPromptValue
                                loPromptValues.Value = strPromptValueArr(x)
                            End If

                        Console.WriteLine("Error processing delimited prompt: '" + strCurrPromptValue + "'")
                        On Error Resume Next
                        Exit Sub
                    End If

                Next j
            Next i


It might be nicer, but I am not a developer :slight_smile: The String2Array function splits a string by a delimiter and loads it to a public array (strPromptValueArr). I think, the rest is self-speaking :slight_smile:

Thank you for all the help!


vilmarci :netherlands: (BOB member since 2006-10-04)

Hi BOBJ Gurus!

I am using the following code:

Sub test()

Dim qry As Object
Dim pr As webi_moduleLib.ILiveObjects
Dim v

Dim loObj As IWebiAddinDocument
Dim loPrompt As WebiAddinPrompt
Dim loPromptValues As WebiAddinDiscretePromptValue

Set lo = Application.COMAddIns("CrystalOfficeAddins.CrystalComAddin.7").Object ' findCorrectCOMAddIn.Object

Set loObj = lo.LiveObjects.GetItem(0)
Debug.Print TypeName(loObj)

Set loPrompt = loObj.WebiAddinPrompts.GetItem(0)
Debug.Print TypeName(loObj.WebiAddinPrompts.GetItem(0))

Set loPromptValues = loPrompt.CurrentValues.GetItem(0)
Debug.Print TypeName(loPrompt.CurrentValues.GetItem(0))

loPromptValues.Value = "some value"

loPrompt.AddValue loPromptValues, True

loObj.WebiAddinPrompts.Add loPrompt


End Sub

My goal is to refresh all objects with a prompt value. I already added cyrstal_addin_framework and webi_module libs. I tried to follow the steps you already described but I did not need an on document open event so did not used it.

LO starts retrieving data form the server stops in the middle and never even gets to rendering. At that point VBA execution resumes but data is not refreshed at all or the prompt is not accounted for.

Interesting thing is that if I do a manual refresh I get the value from my code in the selected prompt value and I need to only click ‘ok’ and refresh is happening normally.

I am using SAP BusinessObjects™ Live Office XI 4.2

Any help would be welcome !


mkolev (BOB member since 2017-03-16)

Have you made any progress with this as I am experiencing the same
issue with BO 4.2.

The webi query appears to be launched to the server but the subsequent dialog identifies Live office as changing defaults and security settings. It renders no data back as if the prompts have been ignored.

Paul Coates (BOB member since 2008-02-29)