I would like to know how can we put this macro in a loop so that it gets all the infformation like universe name and connection names in that repository automatically.
Dim des_app As Designer.Application
Dim des_unv As Designer.Universe
Dim univ_name As Designer.Universe
Dim Wkst As Excel.Worksheet
Sub main()
'Logon and Open Universe
Set des_app = New Designer.Application
des_app.Visible = False
des_app.LogonDialog
Set des_unv = des_app.Universes.Open
'Create worksheet object
Set Wkst = ThisWorkbook.Sheets("New")
'Wkst.Cells.Clear
Wkst.Cells(2, 1) = des_unv.Name
Wkst.Cells(2, 2) = des_unv.Connection
des_unv.Close
des_app.Quit
MsgBox ("Done")
End Sub
I don’t have an example, but here are a few thoughts to nudge you in the right direction. Since you used the .LogonDialog method, I assume this is XI.
Because universes are organized into folders, you will need to create a recursive procedure (meaning the procedure calls itself) passing the folder to be processed each time. Start with the .UniverseRootFolder, then its .Folders collection, repeated as needed. Within each folder, loop through the .StoredUniverses collection … opening, recording, and closing each one.
The recursion technique used for classes and objects in this utility can be used as an example. Hope this helps.
For Each ObjFolder In ObjDes.UniverseRootFolder.Folders
Debug.Print ObjFolder.CUID
Debug.Print ObjFolder.Name
For Each ObjStoredUniverse In ObjFolder.StoredUniverses
Debug.Print "------" & ObjStoredUniverse.CUID
Debug.Print "------" & ObjStoredUniverse.Name
Next ObjStoredUniverse
Next ObjFolder
But the problem at this stage is how to get all the properties of universe class…
i.e. at the StoreUniverse level we get only get: .CUID .Name .LockBy .Parent
Thats all!
Dim designerApplication As New Designer.Application
Dim universe as Designer.universe
Call designerApplication.Logon("login", "password", "server", "secEnterprise")
For universe To designerApplication.universes
nbrUniverses = nbrUniverses = + 1
Next universe
The result will be (designerApplication.universes.Count = 0)…
I want to loop all the universes in my repository
And by the way, get information as properties, tables use, classes, objects…
Its very similar as the Dwaynes utility but for multi-universes.
Now, if its possible I wondering how to make the relation between the StoredUniverse collection and the universe Collection
You would loop through the .StoredUniverse collection to get enough information to import, then open, each universe. Opening the universe puts it in the .Universe collection, but at that point it’s moot. You’ll only have one universe open at a time.
Not possible. The .Import method does not return an object. Drop the Set ObjUniverse = part and the method will work. It simply imports the universe to the local hard drive.
Give the full pathname to the universe on your local hard drive, and it will open the universe.
Combines the .Import and .Open into a single method.
I was to accomplish what you need by querying cms. Here is a sample (I assume you know how to run a cms query):
Set unvs = iStore.Query("SELECT SI_ID, SI_PARENT_FOLDER_CUID,SI_NAME FROM CI_APPOBJECTS WHERE SI_PROGID='CrystalEnterprise.Universe'")
For i = 1 To unvs.Count Step 1
Set unv = unvs.Item(i)
unvParent = unv.Properties.Item("SI_PARENT_FOLDER_CUID")
unvName = unv.Properties.Item("SI_NAME")
Set Univ = DesApp.Universes.OpenFromEnterprise(unvParent, unvName, False)
' Add code below to retrieve properties you need
Next