Property bags - how to access them in VBA

sdk development
1

Hi,

I downloaded one of the spreadsheets from this site that documents information about a CMS (Universes, reports, etc.)

I have added another tab to the spreadsheet with User information - I’m able to access all the “top-level” properties using this code and write this to the spreadsheet sing this code:

Private Sub getUsers()

'Purge existing data
    Sheets("User Information").Range("A2:IV65536").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 UserList As InfoObjects
    'Info Object declaration
    Dim UserItem As InfoObject
    'User Object declaration
    
    Dim Rng As Excel.Range
    Dim RowNum As Long
    Dim strSQL As String
    Dim i As Integer

    
    On Error GoTo ErrorHandler
    'Session Manager instanciation
    Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
    'Enterprise Session instanciation
    Set esession = SessionManager.Logon(tbName, tbPassword, tbCMS, strAuth)
    
   strSQL = "Select top 10000 SI_ID, SI_NAME, SI_USERFULLNAME,SI_EMAIL_ADDRESS, SI_CREATION_TIME, SI_LASTLOGONTIME, SI_UPDATE_TS, SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'User' and SI_UserFullName != '' "

    'Infostore instanciation
    Set iStore = esession.Service("", "InfoStore")
    Set UserList = iStore.Query(strSQL)
    Set Rng = Sheets("User Information").Cells
  
    RowNum = 1
    Rng(RowNum, 1) = "User ID"
    Rng(RowNum, 2) = "Name"
    Rng(RowNum, 3) = "User Full Name"
    Rng(RowNum, 4) = "Email"
    Rng(RowNum, 5) = "Date Created"
    Rng(RowNum, 6) = "Last Login"
    RowNum = RowNum + 1
     
For Each UserItem In UserList
           
          Rng(RowNum, 1) = UserItem.ID
         
          For i = 1 To UserItem.Properties.Count
            If UserItem.Properties.Item(i).Name = "SI_NAME" Then
                Rng(RowNum, 2) = UserItem.Properties.Item(i).Value
            End If
            
            If UserItem.Properties.Item(i).Name = "SI_USERFULLNAME" Then
                Rng(RowNum, 3) = UserItem.Properties.Item(i).Value
            End If
            
            If UserItem.Properties.Item(i).Name = "SI_EMAIL_ADDRESS" Then
                Rng(RowNum, 4) = UserItem.Properties.Item(i).Value
            End If
            
             If UserItem.Properties.Item(i).Name = "SI_CREATION_TIME" Then
                Rng(RowNum, 5) = UserItem.Properties.Item(i).Value
            End If
             If UserItem.Properties.Item(i).Name = "SI_LASTLOGONTIME" Then
                Rng(RowNum, 6) = UserItem.Properties.Item(i).Value
            End If
            
         Next i
          
        RowNum = RowNum + 1
 
      Next UserItem
        
 End Sub

I want to access the SI_ALIASES properties (i.e. SI_NAME, SI_DISABLED, SI_ID and SI_TOTAL). My understanding is that I have to use “Property Bags” to get at these properties.

Can anyone point me in the right direction to access these properties using VBA?

sunsethill :canada: (BOB member since 2007-01-29)

2

It’s recursive:

object.Properties("SI_ALIASES").Properties("SI_TOTAL")

Joe

joepeters :us: (BOB member since 2002-08-29)

3

That works for SI_TOTAL - but not for SI_NAME, SI_DISABLED and SI_ID…

sunsethill :canada: (BOB member since 2007-01-29)

4

Right, because those are subproperties again:

object.Properties("SI_ALIASES").Properties("1").Properties("SI_NAME")

joepeters :us: (BOB member since 2002-08-29)

5

Thanks, I have it working now.

I was trying:

object.Properties("SI_ALIASES").Properties(1).Properties("SI_NAME")

Never figured the property index would require quotes…

Your assistance is very much appreciated. :smiley:

sunsethill :canada: (BOB member since 2007-01-29)

6

Well, it’s not an index – it’s the property name.

If you iterated through them, you’d find:


Index    Name           Value
1        SI_TOTAL       2
2        1              (Property bag for ID 1)
3        2              (Property bag for ID 2)

So, what I would do is:

For x = 2 to object.Properties("SI_ALIASES").Properties.Count
   Set myProp = object.Properties("SI_ALIASES").Properties.Item(x)
   debug.Print myProp.Properties("SI_NAME")

This skips over the first property, which is the SI_TOTAL property.

Alternatively, you could call the property by name, as I did in my previous post.

Joe

joepeters :us: (BOB member since 2002-08-29)