Schedule a VBA macro based DeskI Report.

[Former Member]

Hi,

We have a requirement to schedule a VBA macro based report via CMC and Infoview and I would like to acheive the below. Please help.

1) To save the report output as CSV file after refresh in Xi R2 and I have the following code, which runs fine if I run manually, but if I schedule it in Xi R2, it does not run.Please help.

Private Sub Document_AfterRefresh()

Dim boDP As busobj.DataProvider

Set boDP = ThisDocument.DataProviders.Item(1)

OUTPUT_FILE_CSV = TARGET_FILE_DIR & OUTPUT_FILE_NAME & Format(Now, "YYYYMMDD") & "_" & Format(Now, "hhnnss") & ".csv"

Call boDP.ConvertTo(5, 1, OUTPUT_FILE_CSV)

End Sub

The OUTPUT_FILE_CSV & TARGET_FILE_DIR are declared as global varilable. Please help.

2) A prompt to be filled and the following syntax works fine when I run the macro manually. But does not work when I schedule in CMC (Variable prevented report to refersh error)..

Private Sub Document_BeforeRefresh(Cancel As Boolean)

newfromdate = DateAdd("M", -4, DateValue(Now))

Application.Variables.Item("BOL Date").InterpretAs = boStringVariable

Application.Variables.Item("BOL Date").Value = newfromdate

End Sub

Please advice.

Thank You.

[Former Member]

Hi Sundaresan,

Following are the important points related to scheduling deski report containing VBA macro:

1. BusinessObjects XI Release 2 supports macros in

Desktop Intelligence in InfoView, both when viewing or

scheduling the document, but with caveats.

2. There are slight differences in the execution flow when

viewing a Deski in InfoView as opposed to viewing on

the Desktop Intelligence client.

3. A restriction on scheduling documents is that the

scheduler can only open one document at a time u2013 so

if you have macros that try and access another Deski

document, it will fail.

4. Whether a macro executes successfully or not would

depend on the functionality used in the macro.

May i know which service pack you are using,as this is known issue and it works fine with BOXI R2 SP2 and higher versions.

I hope this helps you.

Regards,

Snehal

[former_member190767]

Hi Sundaresan,

Within the XI platform, the DeskI reports are not being handled by the busobj (=deski client) process as it was done in the previous versions. The XI platform has a dedicated procedure (fcproc) that handles the processing of DeskI documents on the server (both by infoview as scheduling).

The fcproc is designed to handle the request better and faster via the web then the classic approach, diasadvantage in this new concept, is that there is no SDK/VBA support build in. If you have DeskI documents that are running VBA code to execute several tasks, the code simply will not trigger itself.

There are two work arounds in this:

- Build a VBA prodedure that runs as a job program and schedule this in the DeskI Client (server side)

- use the default functionality of the scheduler, although you can not schedule to CSV, you have a (plain) text option.

Cheers

Merlijn