Julien Bras - I used Julien’s code from the “BOXI UserList & Group Extraction” as a base for this macro.
jrennatc from the Tek-Tips forum - I used jrennatc’s code to get the number of instances, recurring instances and avg runtime.
Required: Microsoft Excel 2007 / XI 3.1
Version: 2.0
Rather than post the code verbatim, here is an overview of the logic:
1. Connect to the CMS (Thanks Julien!)
2. First Loop - Loop through the three types of reports: Crystal, Webi, Deski
2. Second Loop - Extract the list of reports.
3. Third Loop - Count up the number of instances and calculate the avg runtime and sum the size of the instance files in MB.
Thanks Jim! This is a great utility and is going to be tremendously helpful. As you indicated in your email, only took changing the references to XIR2 dll’s to get it working on my system.
Trying to open it in EXCEL 2003 (corporate standard) - but it gives the “Uninitialized Activex Controls Will Not Be Opened in This Version of Excel”
The Microsoft notes on this issue say -
If a workbook contains ActiveX controls that are considered to be Unsafe for Initialization (UFI), they are lost when you save the workbook to an earlier Excel file format. You may want to mark those controls as Safe for Initialization (SFI).
Any chance that you can change that setting on the XLSM file, so that we should be able to open it OK in EXCEL 2003…?
I confess I rarely do anything with macros, so this may be obvious, but when I try to run it, I get “Compile Error Can’t find project or library”. Is there something else I should do?
Also, a couple questions:
Our install is Linux - will this work for that, or is it assuming Windows?
What’s the format for entering the CMS name - any port or anything?
Any sense of how long it runs, or whether this should be done off hours?
I am running into an error when I try to run the macro. The error is: “Access denied. You cannot log on to an older version of the CMS.”
I am running XI3.1 SP3 FP3. How do I get the macro to work for my version?
I install office 2007 nad when try to run the macro I found this error:
Private Sub cbExtract_Click()
Application.ScreenUpdating = True
Dim ModeRecalcul As Long
ModeRecalcul = Application.Calculation
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = False
'Purge existing data
Sheets(“Reports”).Range(“A3:L65000”).ClearContents
'Session Manager declaration
Dim SessionManager, Sess As SessionMgr <-----ERROR at this line
'Enterprise Session declaration
Dim esession As EnterpriseSession
'InfoStore declaration
HI All:
Seems to be a great tool. It blows up when reach about the report 1800. Our Filstore got so many reports and instances that the macro is erroring out and I need to check the code and validate it doesn’t have any restrictive setting as an array or variable declaration being too short.
(BOB member since 2006-01-04)
(BOB member since 2007-10-24)
.
(BOB member since 2007-03-23)
(BOB member since 2005-12-20)
(BOB member since 2004-10-01)