Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

BO VBA 6.5.1: Getting Document Refresh Parameter Values

Hi guys.

Please help a novice in BO 6.5.1 development. I've been tasked to write a macro to iterate through a number of legacy documents and to export data for all the possible values of the different parameters. I figured out how to hardcode a value and how to get the current value, how to get the data provider SQL query (generally huge and ugly), but could someone point me to an example of how to get other possible values? The Variable.PossibleValues property call leads to an error stating that the Variable does not have a list of possible values, but when a report is opened, the user can select a value from a list. Do I have to somehow perform a SQL query directly to get these values? Is a general purpose solution possible or do I have to hardcode the parameter values (getting them from a predefined list or file)?

Thank you in advance

Follow-up: What is done

I prefer using the collections rather than cycles and external list files rather than hardcoded values.

...
Const folder = "C:\...\BusinessObjects\97\"
Const listFile = "C:\...\list.txt"
Sub Get()
Dim x As Variable
Dim dp As DataProvider
Dim c As Column
Dim rep As Report
Dim doc As Document
Dim docNames As New Collection
Dim name, path$, f#, str$

f = FreeFile
path = Left(folder, Len(folder) - 3)
On Error GoTo ErrHandle1
Open listFile For Input As f
While Not EOF(f)
  Line Input #f, str
  docNames.Add(str + ".rep")
Wend
Close
On Error GoTo 0

For Each name in docNames
  On Error GoTo ErrHandle2
  With Application
    .Documents.Receive name, path
    Set doc = .Documents.Open(path + name, True, True)
    For Each dp In doc.DataProviders
      Debug.Print dp.SQL
      ' Here it is also possible to view the data already filtered with the last used values of the variables
      ' using dp.Columns, c.Item(index), etc.
      ' Getting the unfiltered data might also help
      ' The problem is in the "general purpose" part, because inferring the possible values from an ugly 
      ' SQL query might require an unsurmountable amount of logic
    Next dp
    For Each x In doc.Variables
      If InStr(1, x.name, "Year") > 0 Then x.Value = 2008
      '...
    Next x
    .Interactive = False
    doc.Refresh
    .Interactive = True
    For Each rep In doc.Reports
      On Error GoTo ErrHandle3
      rep.ExportAsExcel folder + rep.name + ".xls"
    Next x
  End With
Next name


ErrHandle[1-N]:
End Sub

Edited by: Alexey Krasheninnikov on Oct 2, 2008 10:00 AM

Edited by: Alexey Krasheninnikov on Oct 2, 2008 10:21 AM

Edited by: Alexey Krasheninnikov on Oct 2, 2008 10:43 AM

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question