How to reference a prompt value in VBA

system · February 3, 2003, 7:15pm

Please, have patience with me, I am no VB programmer…
I am trying to run the following code:

Dim h1 As DocumentVariable
Set h1 = ThisDocument.DocumentVariables("VAR_prompt_value")
MsgBox "hello :  " &amp; h1

But it gives me the error 438 object does not support this property or method.

VAR_prompt_value is the name of a local report variable defined in the BO document

Can someone help me out?

Andreas (BOB member since 2002-06-20)

system · February 3, 2003, 7:35pm

So far so good. You have created a variable of type DocumentVariable. You have assigned a local document variable to this item.

Nope, not going to work. The document variable has a number of attributes (like name, formula, etc.). You need to pick something out of the variable and use that.

That’s the BO way of saying, “You can’t do that with this.” You have a document variable, which is a parent object. You need a property from that object - like the name, or something - as mentioned earlier.

If you want the values for that variable, then you need to use a variant (array type) to store them in. Something like:

dim h1_values as variant
h1_values = h1.values(boUniqueValues)

Next, you can determine how many values there are using the UBound() function, and print out each value in a loop.

intNumValues = UBound(h1_values)
for i = 1 to intNumValues
   msgbox "Value number " &amp; i &amp; " is " &amp; h1_values(i)
next i

Does this help you get what you were looking for?

Dave

Dave Rathbun (BOB member since 2002-06-06)

system · February 3, 2003, 7:56pm

Yes, thank you very much.
I am referencing the local report variable which is defined via UserResponse in the BO document to archive the BO document as an excel spreadsheet with the file name “xyz archive promptvalue.xls”, for example “QC archive 2-1-2003.xls”

Andreas (BOB member since 2002-06-20)

system · February 3, 2003, 9:35pm

Since you are talking specifically about user prompts, there is another way to reference the choice via VBA.

Dim Var as Variable
Set Var = ThisDocument.Variables("desired text of prompt here")
MsgBox "Prompt value:  " &amp; Var.Value

Using this approach, there would be no need to have a DocumentVariable with the UserResponse formula.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · March 5, 2003, 9:57pm

This doesn’t seem to work either.

Unless I am misunderstanding what the “desired prompt of text” actually is.

I have put the following in:


Dim RepID as Variable
Set RepID = Thisdocument.Variables("Report ID:")

Report ID: being the actual prompt text. I am getting “Cannot find variable by its name” error on this. I have tried a variety of ways and get this error message or the 408 deal.

I am trying to pass a value in prompt to be used as the file name for saving data provider and report information into excel.[/code]

estaup (BOB member since 2002-10-15)

system · March 6, 2003, 1:51pm

estaup:

Dim RepID as Variable
Set RepID = Thisdocument.Variables("Report ID:")
Report ID: being the actual prompt text. I am getting “Cannot find variable by its name” error on this. I have tried a variety of ways and get this error message or the 408 deal.[/code]

Hmmm… not sure why that didn’t work. This code will list all variable names in the immediate window, so you can verify that the variable exists.

Sub ListVars()
    Dim Var As Variable
    For Each Var In ThisDocument.Variables
        Debug.Print Var.Name
    Next Var
End Sub

Actually, I can’t even generate your error at all. I did the following

Dim Var as Variable
Set Var = ThisDocument.Variables("Bogus")

and it didn’t give me an error. It simply added Bogus as a new variable with no message at all. If you’re still having trouble, you might want to post the more complete code to see if it might trigger other ideas.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · March 6, 2003, 5:03pm

Ok. I thought that there might be something else in the VB code causing the issue. So I took a fresh copy of the document and did the setup.

Added the following to the where clause of my query: <> @variable(‘Report ID:’)
Created a variable in the report named ReportID which is:
=Trim(UserResponse(“DEProduction” ,“Report ID:”))
added this for AfterRefresh(): (no other VB exists)


Private Sub Document_AfterRefresh()
    Dim var As Variables

    Set var = ThisDocument.DocumentVariables("ReportID")

End Sub

When it runs I now receive a ‘Type Mismatch’ Error. I have tried everything I can think.

I must be missing something, somewhere.

estaup (BOB member since 2002-10-15)

system · March 6, 2003, 5:23pm

estaup:

Private Sub Document_AfterRefresh()
    Dim var As Variables

    Set var = ThisDocument.DocumentVariables("ReportID")

End Sub
When it runs I now receive a ‘Type Mismatch’ Error. I have tried everything I can think.

I must be missing something, somewhere.

You have typed var as a Variable but are trying to use it as a DocumentVariable. That’s an easy thing to miss. Variables are application items, DocumentVariables are document items.

Also, Variables is a collection, if you want a single member of the collection, use the singular. As in:


Private Sub Document_AfterRefresh()
    Dim var As busobj.DocumentVariable

    Set var = ThisDocument.DocumentVariables("ReportID")

End Sub

Dave

Dave Rathbun (BOB member since 2002-06-06)

system · March 6, 2003, 5:44pm

Thanks Dave. That helped.

However…

I can obtain any information about the variable I want except for the piece that I want. The value of the variable. I still get a type mismatch error or a compile error if I try and use VAR.Values.

If I use VAR.Formula I get the formula of the variable.

How do I successfully obtain the value of the variable. I am combing the group and trying everything I can find, but not able to get a value.

estaup (BOB member since 2002-10-15)

system · March 6, 2003, 5:49pm

SUCCESS!!!

This is what finally worked.


Private Sub Document_AfterRefresh()
    Dim Var As busobj.DocumentVariable
    Dim intVarCount As Integer
    Dim myDocument As busobj.Document
    Dim xlname As Variant
                
    Set myDocument = ThisDocument
    Set Var = ThisDocument.DocumentVariables("ReportID")
    xlname = Var.Values(boUniqueValues)
    
    MsgBox xlname(1)
End Sub

Thanks for everyone help on this.

estaup (BOB member since 2002-10-15)

system · March 6, 2003, 6:20pm

I have series of functions I wrote some time ago an drop them into most of the VBA work that I do. Included in those utilities is:

Function GetVariableValueCount(ByVal strVarname As String, ByVal intUniqueFlag As Integer) As Integer

'   Parameters
'       strVarname      A string containing the name of the variable to check
'       intUniqueFlag   An integer value referencing a BO Constant that determines
'                       how the values are treated. Options are boAllvalues or boUniqueValues
'
'   Return Value
'       Integer value
'
'   Parameters passed by Value to ensure that this routine does not change the values.
'
'   This function scans the selected variable in the document and returns the number of values
'   found. The count can be a unique count (boUniqueValues) or a count of all entries (boAllValues)
    
    Dim intNumChoices As Integer
    Dim varRequest As Variant
    Dim boRequestVar As busobj.DocumentVariable
    
    Set boRequestVar = boDoc.DocumentVariables(strVarname)
    GetVariableValueCount = UBound(boRequestVar.Values(intUniqueFlag))
    Exit Function
    
End Function

Function GetVariableValue(ByVal strVarname As String, ByVal intUniqueFlag As Integer) As Variant

'   Parameters
'       strVarname      A string containing the name of the variable to check
'       intUniqueFlag   An integer value referencing a BO Constant that determines
'                       how the values are treated. Options are boAllvalues or boUniqueValues
'
'   Return Value
'       Variant, see below for details. May contain a string or array of strings.
'
'   Parameters passed by Value to ensure that this routine does not change the values.
'
'   This function scans the selected variable in the document. If requested, it will verify
'   the uniqueness of the value. The return item is either the unique value in a string, or
'   an array of strings containing all of the requested items.
    Dim boDocVar As busobj.DocumentVariable
    Dim boVariableValues As Variant
    
    Set boDocVar = boDoc.DocumentVariables(strVarname)
    boVariableValues = boDocVar.Values(intUniqueFlag)
    
    If (intUniqueFlag = boUniqueValues) And (GetVariableValueCount(strVarname, intUniqueFlag) > 1) Then
        Call LogEntry("GetVariableValue", "Duplicate values found where unique expected.")
    Else
        If (intUniqueFlag = boUniqueValues) Then
            GetVariableValue = boVariableValues(1)
        Else
            GetVariableValue = boVariableValues
        End If
    End If
    Exit Function
    
End Function

Dave Rathbun (BOB member since 2002-06-06)

system · May 20, 2009, 11:04pm

Hi folks, was anyone ever able to find a way of listing the values of report
prompts in a report?

regards,
Gino

BOBJGT (BOB member since 2005-12-14)

system · May 21, 2009, 2:33pm

If you are wanting to just redisplay the values supplied by the user in the prompt in the report use the following:


=UserResponse(DataProvider(<YourDataProviderName>) ,"YourPromptTextHere")

If you are wanting the value programatically, then the last post by me is the answer. In the example “Report ID:” is the prompts text.

estaup (BOB member since 2002-10-15)

system · May 21, 2009, 8:19pm

Hi,

I am relatively new to VBA coding for Business Objects.

I was able to use some of the VBA code in this thread to get the various prompt values; but, can not figure out how to get the entire file name which includes various prompt values, text, and date to save as Excel. Included is excerpts from what I’ve been playing with:

Private Sub CommandButton1_Click()
 
    Dim newFile As String
    Dim ProvName As busobj.DocumentVariable
    Dim ProvNum As Variable
    Dim FiscalEnd As Variable
    Dim intVarCount As Integer
    Dim myDocument As busobj.Document
    Dim xlname As Variant
                   
                 
    Set ProvName = ThisDocument.DocumentVariables("Legal Name")
    xlname = ProvName.Values(boUniqueValues)

    Set ProvNum = ThisDocument.Variables("Billing Number")

    Set FiscalEnd = ThisDocument.Variables("From Service Date - End (MM/DD/YY)")
    
    
    
    newFile = xlname(1) &amp; " " &amp; ProvNum.Value &amp; " FPE " &amp; FiscalEnd.Value &amp; Format$(Date, "mm-dd-yyyy")
    

    On Error Resume Next
    ThisDocument.SaveAs (newFile &amp; ".xls")
    
End Sub

Thanks in advance for any help.

borg (BOB member since 2009-05-21)