Help required in VB macro in Business Objects XI3.1 Report

[Former Member]

Hi All,

I'm trying to create a VB macro to split the Data into separate tabs if the Row limit exceeds 65000, so that we can save that in a xls document.

In that i have created a Report Level variable (not a Universe Object. Just a report level variable). I'm trying to assign this Variable to my macro variable. I need the correct syntax for assigning my Report variable to macro variable. The sample code is as below

Report Variable is a measure variable.

Code:

Dim Doc as Document

Dim DocVar as DocumentVariable

Set Doc = ThisDocument

Set DocVar = Doc.DocumentVariables("Report Variable")

Now, when i try to get the value of DocVar in MsgBox (MsgBox DocVar), it is giving the below error

Run-time error '438' : Object doesn't support this property or method.

However, when i execute (MsgBox DocVar.Name), the name of the Report Variable is displaying correctly in the message box.

Anyone please help in getting the correct syntax for setting report variable's value to a macro variable.

[Former Member]

I am having the same problem!

I want to be able to change the value in a filter and execute a macro to export the data to excel, with the value in the filter included in the file name, but I can't seem to get the value to come through in the macro.  I can get it to return the name of the field as in your example, but when I try to get the value I just get a blank message box. 

Does anyone have any ideas on how to make this work?  I'm working with a user-defined variable that collects the value set for the filter, and setting only one value at a time in the filter, if that helps any.  I was also using slightly different code:

     Var = ActiveDocument.Variables.Item("FieldName").Value

     MsgBox Var

I have tried other code as well, more like what is shown above, but I am not well-versed in VB and nothing I have been able to find has worked for me.  Please help!

[Former Member]

Good day Deepika,

If I understand you correctly you just need to know the point at which to create a second tab in Excel.

Personally I would have created another new workbook - and not just another sheet in the same Excel workbook. In this way you can keep the Excel file size to a minimum.

With regards to splitting the data I would use a record counter, i.e.: increment a field for every record written to the new sheet / workbook. Once you reached your predefined limit you start from ctr = 0 on the new sheet / workbook.

I hope it helps

Blessings