Excel utility with several lists

system · September 29, 2008, 3:21pm

Author: Adrián BO_Worker developed from Julien Bras’ userlist utility.
Author notes: Excel 97-2003 VBA application. Displays some lists for users, groups, universes, reports… Only reading, not writing back. Please go to configuration sheet and fill Server, Username and Password in order to get it working. After that try the rest of sheets pressing the buttons to field the data below. Whenever you get orange cells that’s a place you should fill. If you get Id and Name to fill you can write one or the other. In names you usually can use % character. Some queries can take a long time (10-15 minutes) depending on your server and number of objects involved.

I hope it’s useful for someone and want to thank Julien Bras for previous programming.
BOXIR3_Lists97.zip (108.0 KB)

BO_Worker (BOB member since 2008-09-04)

system · October 6, 2008, 3:10pm

Does it works for XIR2, Can you please help…

sriharshap (BOB member since 2008-10-06)

system · October 6, 2008, 7:04pm

Does this work for excel 2003 now it is giving error.

v_b (BOB member since 2007-12-27)

system · October 7, 2008, 6:39am

Sriharsharp: I haven’t tried it for XIR2, I don’t know whether it works or not.

Vinayakb: It works for Excel 2003, but you should also have the correct libraries (XIR3 libraries) installed for your VBA. Could you please give me more details about your error?

BO_Worker (BOB member since 2008-09-04)

system · October 7, 2008, 2:05pm

I am trying in xir2 and i get the error - Compile error cannot find project or library. when i try to get the report list.

v_b (BOB member since 2007-12-27)

system · October 7, 2008, 2:40pm

Hello, you should change references for libraries in the macros. You should open the code (Alt+F11). Then go to Tools->References. You will see there are some references missing for Crystal Enterprise. You will have the same references below for another version. BOXIR3 version is 12, I think BOXIR2 version is 11.5. You should tick your own libraries to make it work.

Best regards,

Adrián

BO_Worker (BOB member since 2008-09-04)

system · October 7, 2008, 2:41pm

I have to say I haven’t tested it for BOXIR2 so if choosing the references doesn’t work, I think it wouldn’t work without changing the code.

BO_Worker (BOB member since 2008-09-04)

system · October 8, 2008, 2:36pm

Thank you it worked.

v_b (BOB member since 2007-12-27)

system · October 14, 2008, 2:38am

Hi,
Does this Excel requires SDK to be installed on user machine.If so do you know from where i can install SDK for BOXIR2 SP3.

Thank you

karen (BOB member since 2003-06-18)

system · October 14, 2008, 10:50am

Yes, you need SDK installed on your machine. You get it by installing client BOXI in your PC. After installing, as you are using BOXIR2 you will also need to rearrange VBA references in your excel file (as explained up here).

Best regards,

Adrián

BO_Worker (BOB member since 2008-09-04)

system · December 10, 2008, 4:40pm

Hi

i cant get it to work, it says cant find project or library and highlights
Dim SessionManager As SessionMgr, Sess As SessionMgr

bulgaru10 (BOB member since 2006-12-15)

system · December 12, 2008, 4:21pm

Hi, I got that error too when I ran first time, then I changed the VBA References as detailed in an earlier post.

Jamie Alexander (BOB member since 2007-05-15)

system · February 9, 2009, 10:06am

Hi,

I have tried adapting the References (to ver 11.5) in Excel for BOXIR2, but still get caught with the error
Set SessionManager = CreateObject(“CrystalEnterprise.SessionMgr”)
Are they any references I could be possibly missing here?

It would be an incredibly useful tool for me if I could just get it to work!

squash junkie (BOB member since 2008-09-08)

system · February 9, 2009, 1:02pm

You need a reference to Crystal Enterprise Framework Library xx.x. After that, I use this code instead of CreateObject, so I don’t have to go figure out the class (appname.objecttype):

Dim mySessionMgr As CrystalEnterpriseLib.SessionMgr
Set mySessionMgr = New CrystalEnterpriseLib.SessionMgr

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · February 9, 2009, 1:42pm

Thx a lot, that worked a treat.
I had already the reference to the Crystal Enterprise Framework library 11.5, but the change in code sorted it out perfectly!

squash junkie (BOB member since 2008-09-08)

system · February 10, 2009, 4:00pm

Hi,
Thanks for this macro
But I have a problem. I want to list all universes used in each report. But with your macro I can have only one universe.
I have some report with 2 or more universe.
Do you know a solution to list all universes ?

Thanks

coraziari (BOB member since 2007-11-26)

system · March 6, 2009, 6:55pm

Hi this utility is excellent but can we add users in CMC using it or is it possible?

abhisheksharma637 (BOB member since 2008-01-11)

system · March 7, 2009, 6:38pm

Hi Dwayne,
There are several modules, where does this session manager go and do you also need to replace his “SessionManager” with your “mySessionMgr” through the code?

Sorry, not too familiar with VBA for BO and need a little spoon feeding here.

Thanks,
Jon

pcgeekus (BOB member since 2003-10-16)

system · March 7, 2009, 6:55pm

By the way, how do you run this? I don’t see a Macro listed from the Tools Macro list.

pcgeekus (BOB member since 2003-10-16)

system · March 7, 2009, 7:20pm

I found that you click on the Excel icon image on each tab to run it. After replacing the 8 12.0 Crystal references with the same 11.5 ones, I’m getting this error:
‘User-defined type not defined’

The help had this to say,

This happens on these lines:


Dim SessionManager As SessionMgr, Sess As SessionMgr
Dim esession As EnterpriseSession

The libraries I replaced in the References with 11.5 ones are:
Crystal Enterprise Framework
Crystal Enterprise InfoStore
Crystal Enterprise Plugin Manager
Crystal Enterprise User Plugin
Crystal Enterprise UserGroup PlugIn
Crystal Enterprise Report Plugin
Crystal Enterprise Universe PlugIn
Crystal Enterprise Webi PlugIn

How do I get past this error?

Oops, somehow it dropped the Framework Reference and caused this error. Also, I have noted that the session changes suggested by Dwyane must be added to each macro since they run independently.
[img]

Of course, now I’m timing out!
Our repository is big with 14,000 users and over 50,000 reports.
Any thoughts on how to break this code up and make it more manageable for large repositories?

I’ll see about increasing the timeout parameter, but I’d rather break the InfoStore queries into smaller, runable pieces within the default 9 minutes.[/img]

pcgeekus (BOB member since 2003-10-16)