BusinessObjects Board

XI3.1 - Recurring Instance Detail List - Excel 2007 Macro

Author: Jim McGregor,

Author notes:
I wrote this macro to be able to retrieve certain information about recurring instances, specifically event dependency and destination email addresses (if applicable to the schedule). This program is an Excel macro that will extract a list of all recurring instances in the repository and provide some stats about each : Name, update date, create date, parent report folder path, name of event dependency, destination email addresses and output type. This is yet another macro in my series of excel macros I’ve developed for extracting list-type information on reports or instances in my repository.


  • Julien Bras - I used Julien’s code from the “BOXI UserList & Group Extraction” as a base for this macro.

General Code Description: This selects the SI_ID’s of all the recurring instances in the repository (I had 4000+) and loops through them all getting certain information about the parent report template (name, folder path, etc) then it loops through and extracts any information about event dependencies and destination email addresses.

NOTE: This code assumes you have no more than 50,000 instances. That’s the limit I set. You are free to adjust that by editing the code. This is also designed to work only with XI3.1, but you could adapt it to XIR2 by changing the library references. You must also be running Microsoft Office 2007 as this is an “.xlsm” file. If you are still running MS2003, this is a good excuse to get an upgrade from your company.

Enjoy! :smiley:
Report Recurring Instance Info Macro with (35.0 KB)

alpha1145 :us: (BOB member since 2006-01-04)

Hi Jim,

Do you have the file available?
I can’t see it to download :frowning:


Evil Miyagi :uk: (BOB member since 2006-03-06)

Is this hosted anywhere else for download? Could really (really!) use it… :?:

robgt :uk: (BOB member since 2010-04-21)

Is this Macro uploaded somewhere else? Couldn’t see it here. Please refer as this will be really useful for us!


Nits :us: (BOB member since 2008-11-09)

FYI, attachments are not visible in the Uploads forum for non-administrators. They are only visible once they are approved by a site administrator and moved to the Downloads forum.

BoB LoblaW :us: (BOB member since 2007-10-23)

This would be really helpful, any chance it will be available to download soon?

FionaCasey :ireland: (BOB member since 2007-01-22)

Moderator note:
Approved, and moved to BOB’s Downloads.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

Your macro is very helpful.

As oScheduleInfo.Properties(“SI_DESTINATIONS”).Properties(“1”).Properties(“SI_DEST_SCHEDULEOPTIONS”).Properties(“SI_MAIL_ADDRESSES”).Properties(“1”).Value is being used to get the value, could you please guide me how to set this value to my desired dest email?


techytechy (BOB member since 2009-12-03)

Hi there :wave:

I am running this excellent utility at BI4 but I was wondering if anyone had the code to allow it to extract multiple email addresses & the cc addies too?

SandiR :uk: (BOB member since 2004-12-20)

Instead of showing the destination being an email id, is it possible to tweak the code to instead show the destination folder path (if/when a report is being exported to a network folder, instead of being emailed)?

ie. export destination folder path & file name = “\Server\Folder\Subfolder\xxx.xls”

Captspeed :us: (BOB member since 2006-10-03)

Okay, I am probably missing something completely obvious, but I am trying to pull back the SI_NEXTRUNTIME field instead of SI_CREATION_TIME and and can’t seem to get it to work. I simply changed the 5th row to the new field, but that field doesn’t return any data (note that when I randomly try using other fields in the 5th row it seems to work fine).

I can bring back the same information using Query Builder without issue, so I don’t know what I am doing wrong…

Here is the VBA code with the desired change:

RowNum = RowNum + 1
Rng(1, 5) = RowNum - 2
Rng(RowNum, 1) = Mid(rsRow2.Properties(“SI_PROGID”), 19, Len(rsRow2.Properties(“SI_PROGID”)))
Rng(RowNum, 2) = rsRow2.Properties(“SI_ID”)
Rng(RowNum, 3) = rsRow2.Properties(“SI_NAME”)
Rng(RowNum, 4) = rsRow2.Properties(“SI_UPDATE_TS”)
Rng(RowNum, 5) = rsRow2.Properties(“SI_NEXTRUNTIME”)

jbloomfi (BOB member since 2013-03-14)

Hi jbloomfi

How to add Prompts Information and Output Path Information to this version of Macro.

“Recurring Instance Info Macro” has this information but its not working with XI 3.1

Also adding recurrence information (Weekly (or) Monthly Run).

Your help is highly appreciated.


deep (BOB member since 2007-07-24)


Did any one had a chance to modify this Macro to pull the FTP: output path location and Paramaters used in the schedules.

If so could any one upload the Macro or guide me where and how can I get it.

Thank you in Advance.

billa (BOB member since 2008-06-11)

Thank You for your work.
It saved me time… :smiley:


jpenelope (BOB member since 2008-10-17)

I have tried running this but get an error and it returns nothing, if I put in random info in the login screen it gives when you click ‘Connect CMS’ I get the same error, so its like it not even connecting.

I’m I missing something here, I take it by CMS its the same details I would enter under ‘System’ when I login to Designer, and username and pw are login you would use to login to CMC say as an Admin, and not the repo db login / pw ?

Fiona :uk: (BOB member since 2002-12-16)

I was able to get it working by changing the code in the Visual basic

If oScheduleInfo.Properties(“SI_DESTINATIONS”).Properties(“1”).Properties(“SI_PROGID”) = “CrystalEnterprise.Smtp” Then
Rng(RowNum, 11) = oScheduleInfo.Properties(“SI_DESTINATIONS”).Properties(“1”).Properties(“SI_DEST_SCHEDULEOPTIONS”).Properties(“SI_MAIL_ADDRESSES”).Properties(“1”).Value


If oScheduleInfo.Properties(“SI_DESTINATIONS”).Properties(“1”).Properties(“SI_PROGID”) = “CrystalEnterprise.Ftp” Then
Rng(RowNum, 11) = oScheduleInfo.Properties(“SI_DESTINATIONS”).Properties(“1”).Properties(“SI_DEST_SCHEDULEOPTIONS”).Properties(“SI_SERVER_NAME”).Value

saikrishnagd (BOB member since 2014-12-09)


can i use it for SAP BO 4.1 version by changing the Libraries and on client tools?

Nagaveni (BOB member since 2012-08-23)

I don’t think so , it is failing for me … :frowning::frowning:

Else this I believe is the best tool to get required details from the server…

aniketp :uk: (BOB member since 2007-10-05)

I am seeing the same behavior as well. Does anyone know if there is another way to gather this info quickly? Did attempting changing the libraries? Confused on the comment about changing the client tools? :hb:

annwilliams77 (BOB member since 2007-09-28)

The error I get is -
CrystalEnterprise12.SessionMgr1. - -214721105: Enterprise authentication could not log you on. Please make sure your login information is correct.
We are using Windows AD authentication.

aniketp :uk: (BOB member since 2007-10-05)