BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

BOXI UserList & Group Excel Export
3 members found this topic helpful
Goto page Previous  1, 2, 3, 4, 5  Next
 
Search this topic... | Search General Discussion... | Search Box
Register or Login to Post    Forum Index -> General Discussion  Previous TopicPrint TopicNext Topic
Author Message
bmalak_98
Forum Member
Forum Member



Joined: 06 Jul 2006

Posts: 26
Location: Nashville, TN


flag
PostPosted: Thu Jan 24, 2008 11:53 am 
Post subject: Re: BOXI UserList & Group Excel Export

I used to be able to use this tool with no problems and I really like it.

However, I'm suddenly getting an error box when clicking on the "Get Users & Groups" button.

Failure in UserGroups()
VBA Project - 429: ActiveX component can't create object 1000429

Attached a screenshot.

Any ideas?
Back to top
alpha1145
Principal Member
Principal Member



Joined: 04 Jan 2006

Posts: 181
Location: Salt Lake City, UT


flag
PostPosted: Thu Feb 28, 2008 4:33 pm 
Post subject: Re: BOXI UserList & Group Excel Export

I LOVE this tool. Thanks Julien! I needed to get a list of users and group membership, however I wanted a list that would repeat the user record for each group the user was a member of. That way I could give a department a list of everyone that was a member of their particular group. All I had to do was change Julien's code just a little to get it to work. Here's what I did:
Code:

Private Sub cbExtract_Click()
    Application.ScreenUpdating = False
    Dim ModeRecalcul As Long
    ModeRecalcul = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    'Purge existing data
    Sheets("Users").Range("A3:L65000").ClearContents
    'Session Manager declaration
    Dim SessionManager, Sess As SessionMgr
    'Enterprise Session declaration
    Dim esession As EnterpriseSession
    'InfoStore declaration
    Dim iStore As InfoStore
    'Info Objects declaration
    Dim Users, Groups As InfoObjects
    'Info Object declaration
    Dim UserItem, GroupItem As InfoObject
    'User Object declaration
    Dim UserObject As User
    Dim Rng As Excel.Range
    Dim RowNum, GroupNum As Long
    On Error GoTo ErrorHandler
    Dim ErrorState As String
    'Session Manager instanciation
    Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
    'Enterprise Session instanciation
    Set esession = SessionManager.Logon(tbName, tbPassword, tbCMS, "secEnterprise")
    'Infostore instanciation
    Set iStore = esession.Service("", "InfoStore")
    'document the users
    Set Users = iStore.Query("SELECT TOP 1000000 SI_EMAIL_ADDRESS, SI_FORCE_PASSWORD_CHANGE, SI_NAME, SI_ID, SI_USERGROUPS, SI_USERFULLNAME, SI_ALIASES, SI_DESCRIPTION, SI_LASTLOGONTIME, SI_PASSWORDEXPIRE FROM CI_SYSTEMOBJECTS Where SI_KIND='User'")
    RowNum = 2
    Set Rng = Sheets("Users").Cells
    'Write in the top the server/login used, the update date
    Rng(1, 4) = "Server: " & tbCMS & Chr(10) & "User: " & tbName & Chr(10) & "Update Date: " & Date & " " & Time
    For Each UserItem In Users
    Set UserObject = UserItem
   
        For Each GroupId In UserObject.Groups
        Set Groups = iStore.Query("SELECT SI_NAME, SI_DESCRIPTION FROM CI_SYSTEMOBJECTS Where SI_ID=" & GroupId)
   
        RowNum = RowNum + 1
        Rng(RowNum, 1) = UserItem.ID
        Rng(RowNum, 2) = UserItem.Title
        ErrorState = "FullName"
        Rng(RowNum, 3) = UserObject.FullName
        Rng(RowNum, 4) = UserObject.EmailAddress
        GroupNum = 0
'                For Each GroupId In UserObject.Groups
'                GroupNum = GroupNum + 1
'                    Set Groups = iStore.Query("SELECT SI_NAME FROM CI_SYSTEMOBJECTS Where SI_ID=" & GroupId)
'                    If (GroupNum = 1) Then
'                        Rng(RowNum, 5) = Groups(1).Title
'                    Else:
'                        Rng(RowNum, 5) = Rng(RowNum, 5) & Chr(10) & Groups(1).Title
'                    End If
'                Next GroupId
        Rng(RowNum, 5) = Groups(1).Title
        Rng(RowNum, 6) = Groups(1).Description
        If (UserObject.Aliases(1).Disabled) Then
            Rng(RowNum, 7) = 1
        Else
            Rng(RowNum, 7) = 0
        End If
        If (UserObject.ChangePasswordAtNextLogon) Then
            Rng(RowNum, 8) = 1
        Else
            Rng(RowNum, 8) = 0
        End If
        Rng(RowNum, 9) = UserObject.Description
        ErrorState = "LastLogon"
        Rng(RowNum, 10) = UserObject.Properties("SI_LASTLOGONTIME")
       
     Next GroupId
       
    Next UserItem
    Application.Calculation = ModeRecalcul
    Calculate
    Application.EnableEvents = True
    Me.Hide
CleanUp:
    Me.Hide
    On Error Resume Next
    esession.Logoff
    Application.Calculation = ModeRecalcul
    Calculate
    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    If Err.Number = -2147210697 Then
        If ErrorState = "FullName" Then Rng(RowNum, 3) = "Error on Full Name"
        If ErrorState = "LastLogon" Then Rng(RowNum, 10) = ""
        Resume Next
    End If
    Me.Hide
    MsgBox Err.Source & " - " & Err.Number & ":  " & Err.Description & " " & Err.HelpContext, _
        vbCritical, "Failure in UsersGroups()"
    Resume CleanUp
End Sub


All I had to do was move the loop for the groups to be inclusive of all the user fields and it works perfectly.

_________________
BOE 4.1 SP6
3 Server Cluster - 24 CPU, Front-End Load Balancing
Teradata : Oracle : SQL Server : DB2 : Greenplum : SAS : Hadoop
Back to top
reemagupta
Forum Enthusiast
Forum Enthusiast



Joined: 18 Sep 2002

Posts: 1207
Location: Houston, TX



PostPosted: Tue Mar 04, 2008 10:09 am 
Post subject: Re: BOXI UserList & Group Excel Export

Thanks for putting this useful tool.
I am getting an error using this tool when I click the "Extract data from CMS" button . The error is "Compile Error: Can't find project or library"

Any idea?

Thanks
Reema
Back to top
Dwayne Hoffpauir
Forum Groupie
Forum Groupie



Joined: 19 Sep 2002
ASUG Icon
medal_gold.gif*2speaker.gif*5medal_bronze.gif
Posts: 8644
Location: Plano, TX USA


flag
PostPosted: Tue Mar 04, 2008 10:13 am 
Post subject: Re: BOXI UserList & Group Excel Export

reemagupta wrote:
I am getting an error using this tool when I click the "Extract data from CMS" button . The error is "Compile Error: Can't find project or library"

The SDK (specifically the COM version of the Enterprise SDK) has to be installed on the machine. If you did a client install (which installs EVERYTHING, including the SDK), you should be fine. If you did a selective install (or no client install) then that would explain it.

_________________
Dwayne Hoffpauir
Image link
Back to top
reemagupta
Forum Enthusiast
Forum Enthusiast



Joined: 18 Sep 2002

Posts: 1207
Location: Houston, TX



PostPosted: Tue Mar 04, 2008 10:28 am 
Post subject: Re: BOXI UserList & Group Excel Export

Dwayne,

Thank You for your reply. I tried it on other client machine and it works perfect.


Regards
Reema
Back to top
surreal
Forum Member
Forum Member



Joined: 29 Aug 2007

Posts: 23
Location: NC


flag
PostPosted: Thu Mar 06, 2008 9:28 am 
Post subject: Re: BOXI UserList & Group Excel Export

I was using this tool and loving it but all of a sudden when I try to Extract Data from CMS now, I get an error:

"VBAProject - 13: Type Mismatch 1000013"

Any ideas what this error means? I haven't changed anything in my XI install and have everything installed. Any help would be greatly appreciated. Thanks.
Back to top
JohnBClark
Forum Enthusiast
Forum Enthusiast



Joined: 27 Mar 2007
ASUG Icon
Posts: 1039
Location: Appleton, WI USA


flag
PostPosted: Mon Apr 21, 2008 10:51 am 
Post subject: Re: BOXI UserList & Group Excel Export

Nice tool, very helpful. I have one question about what I am seeing in our system.

Our setup: We use Active Directory to manage our users. We add the new Active Directory Group as usual using the Authentication tab in the CMC. We also have a process using the SDK that runs at night to update the existing authenticated Active Directory groups to add or remove members.

When I run this spreadsheet, all users have 0 listed for Never Connected. I assume that this correlates to True meaning that all users have connected. There are Last Login Time dates for about half of the users. I am assuming that this is because even though they have been setup using Active Directory, they have yet to log in.

My assumption here is that the Never Connected status is impacted by the Active Directory updates so this is why they all show True. In this case, the Last Login Time is the more accurate information for whether or not they have logged into the system.

Any comments or clarification on my assumptions?

_________________
BusinessObjects BI4.2sp4, SQL Server 2016 (CMS)
Clustered for fail over redundancy
Reporting Tools: Crystal Reports 2016sp4, Web Intelligence
Operating System: Windows 2016 running on VMWare
Reporting databases: SQL Server, Teradata, DB2, Oracle, Hive
Information Steward 4.2sp10
History: BO5, BO6, XIR2, XI3.1, BI4.1.x
Back to top
acook
Forum Member
Forum Member



Joined: 12 May 2008

Posts: 3



PostPosted: Mon May 12, 2008 11:20 am 
Post subject: Re: BOXI UserList & Group Excel Export

This is a great tool. Thanks for sharing. Is there any way to create an odbc data source to the database and then write a Crystal Report that provides the same info?
Back to top
Snabbles
Forum Member
Forum Member



Joined: 07 Jul 2006

Posts: 15



PostPosted: Mon May 19, 2008 9:55 am 
Post subject: Re: BOXI UserList & Group Excel Export

Very nice tool. Thanks.
Back to top
JeroenO
Senior Member
Senior Member



Joined: 16 Sep 2002

Posts: 46
Location: Wageningen, NL


flag
PostPosted: Wed May 28, 2008 4:58 am 
Post subject: Re: BOXI UserList & Group Excel Export

surreal wrote:
I was using this tool and loving it but all of a sudden when I try to Extract Data from CMS now, I get an error:

"VBAProject - 13: Type Mismatch 1000013"

Any ideas what this error means? I haven't changed anything in my XI install and have everything installed. Any help would be greatly appreciated. Thanks.


Same here. We're on SP3 and that's probably the cause. Too bad as this is (was!) a great tool. thumbsup.gif

_________________
Added DS to BO
Back to top
gar06
Forum Member
Forum Member



Joined: 31 Aug 2007

Posts: 24



PostPosted: Wed May 28, 2008 5:55 pm 
Post subject: Re: BOXI UserList & Group Excel Export

This tool is really cool. It's using a VBA code in excel. Does anyone have a vb script version of this code?

Thanks in advance,
Back to top
EricE
Principal Member
Principal Member



Joined: 26 Mar 2008

Posts: 118



PostPosted: Tue Jun 03, 2008 2:37 pm 
Post subject: Re: BOXI UserList & Group Excel Export

Dwayne Hoffpauir wrote:
reemagupta wrote:
I am getting an error using this tool when I click the "Extract data from CMS" button . The error is "Compile Error: Can't find project or library"

The SDK (specifically the COM version of the Enterprise SDK) has to be installed on the machine. If you did a client install (which installs EVERYTHING, including the SDK), you should be fine. If you did a selective install (or no client install) then that would explain it.


I get this same error when trying to use the tool with XI 3.0.
I went back and re-ran the client install and made sure I installed everything. No luck, mine still fails with the error above. The client install doesn't explicetly list the COM version of the SDK. It lists Enterprise Java SDK and Enterprise Web SDK.

Is there somewhere I can download this COM SDK?
Back to top
RedLineE90
Senior Member
Senior Member



Joined: 11 Jun 2008

Posts: 35



PostPosted: Fri Jun 13, 2008 9:37 am 
Post subject: Re: BOXI UserList & Group Excel Export

I am getting a failure in UserGroups()

CrystalEnterprise12.SessionMgr.1 - - 2147024891: 100040[/img]
Back to top
mukul.jain
Senior Member
Senior Member



Joined: 25 Oct 2005

Posts: 51


flag
PostPosted: Sat Jul 19, 2008 7:50 pm 
Post subject: Re: BOXI UserList & Group Excel Export

Where can I get more information to learn how to write such codes,

Thanks In Advance
Back to top
julienbras
Senior Member
Senior Member



Joined: 25 Jul 2007

Posts: 95
Location: Montreal, QC


flag
PostPosted: Wed Jul 23, 2008 5:16 am 
Post subject: Re: BOXI UserList & Group Excel Export

i have just check ; and yes the tool is failing on a BO XI R2 SP3 server. And never tested on BO XI 3.0 icon_smile.gif.


Will try to investigate why it's not working on BO XIR2 SP3 icon_wink.gif

_________________
Julien Bras
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> General Discussion  Previous TopicPrint TopicNext Topic
Page 2 of 5 All times are GMT - 5 Hours
Goto page Previous  1, 2, 3, 4, 5  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0928 seconds using 17 queries. (SQL 0.0030 Parse 0.0613 Other 0.0286)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo