BO VBA 6.5.1: Getting Document Refresh Parameter Values
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