Mass update to object names / descriptions

Update 23-Oct-2009: Another version that can change CLASS names and descriptions can be found here later in this thread.

Author: Dwayne Hoffpauir, EDS Corporation

Further discussion on this utility should take place in this topic.

Author Notes:

Option Explicit                 'require variables to be declared before being used
Dim DesignerApp As Designer.Application
Dim Univ As Designer.Universe
Dim Wksht As Excel.Worksheet

Sub GetInfo()

    Set DesignerApp = New Designer.Application
    DesignerApp.Visible = True
    Call DesignerApp.LoginAs
    Set Univ = DesignerApp.Universes.Open
    DesignerApp.Visible = False

    Set Wksht = ThisWorkbook.Worksheets("Objects")
    Wksht.Unprotect
    Range("Objects").ClearContents
    Call GetObjectInfo(Univ.Classes, 1)
    Range("Objects").Resize(Wksht.UsedRange.Rows.Count - 1, 5).Name = "Objects"
    Range("Objects").Columns("D:E").Value = Range("Objects").Columns("B:C").Value
    Wksht.Protect

    DesignerApp.Quit
    Set DesignerApp = Nothing

End Sub

Sub MakeChanges()

    Dim RowNum As Long
    Dim Cls As Designer.Class
    Dim Obj As Designer.Object
    Dim Rng As Excel.Range

    Set DesignerApp = New Designer.Application
    DesignerApp.Visible = True
    Call DesignerApp.LoginAs
    Set Univ = DesignerApp.Universes.Open
    Set Wksht = ThisWorkbook.Worksheets("Objects")
    Set Rng = Wksht.Range("Objects")

    For RowNum = 1 To Rng.Rows.Count
        Set Cls = Univ.Classes.FindClass(Rng.Cells(RowNum, 1).Value)
        Set Obj = Cls.Objects(Rng.Cells(RowNum, 2).Value)
        If Obj.Name <> Rng.Cells(RowNum, 4) Then Obj.Name = Rng.Cells(RowNum, 4)
        Obj.Description = Rng.Cells(RowNum, 5)
    Next RowNum

End Sub

Private Sub GetObjectInfo(Clss, RowNum As Long)
    Dim Cls As Designer.Class
    Dim Obj As Designer.Object
    For Each Cls In Clss
        For Each Obj In Cls.Objects
            RowNum = RowNum + 1
            Wksht.Cells(RowNum, 1) = Cls.Name
            Wksht.Cells(RowNum, 2) = Obj.Name
            Wksht.Cells(RowNum, 3) = Obj.Description
        Next Obj
        If Cls.Classes.Count > 0 Then
            Call GetObjectInfo(Cls.Classes, RowNum)
        End If
    Next Cls
End Sub

UniverseDescriptions.zip (11.0 KB)


BOB Downloads (BOB member since 2003-05-05)

List of Objects and corresponding tables in Universe
Macro or program for Universe to Excel export
Using Excel to modify and view universe metadata
Object Naming in Upper case
Mass Updating object descriptions in universe
Propagate object name changes to derived universes
Propagate object name changes to derived universes
list of objects
Updating Universe thru SDK in XI
Size of .unv
Update object in Multiple Universes in BI 4.1
Populate Source Information in Mass Change
Bulk adding of Object decriptions to universe possible ?
Bulk update of universe object's names, definition in 4.1
Bulk update of universe object's names, definition in 4.1
[BI4.1] Different name of objects for different users
Divide By 1000
BO 4.1, Upload/Import Descriptions?
Importing Oracle Column Description
Find Universe objects which are not used in reports
Documentation, metada problem?
Turning dimensions to measures by one time
Script to change object names in designer based on colum/tab
Export to Excel (Classes & Objects)
Change Universe and Objects via VBA
Oracle comments in description objects
how to design documentation for universe and reports
Where LOV is used?
Universe Descriptions
Macro - Object Creation
Amending Various Objects
Object Descriptions - BO4 (Rich Client and WebI)
Deriving Object Name from Database
Import Object description from database?? is it possible?
In new schema all tables and col names are changed.
Update Metadata in Designer with Excel data via SDK
Fact table with measure/dimension object problems
Documenting the Universe from Repository
updating UNV_CLASS_DATA?
How to use VBA to get SQL stt of variables and print out?
Whether there is a way to find a Class name of the Object
Need to translate the universe to another language and fast
External Strategies - updating objects with
Properties of Objects in Universe:
object description
Mass Updating object descriptions in universe
Update Descriptions from Oracle to Designer Automatically?
Object Description Automation
New version of mass update utility ( PrefefinedConditions)
Copy objects from one universe to another universe
Auto Rename Object Name in Universe
Universe-Data dictionary-Automate
generating description of objects from the database
New ver of Mass Updating object descriptions in universe
find replace field names using empty string in replace field
How to set all of the Default LOV Uncheck from Properties?
to update multiple objects in a universe
How to get data from excel to universe
Business Objects 5.0 scriptable?
Good Practice - Universe Object Descriptions
How can I write back to universe
Automate Object Description
Reporting on the Metadata in a Universe
How to modify objects in a batch run?
How to modify objects in a batch run?
Dynamic Universe Object Description
Designer XI - Table/Object List
Global object change
Create Class and Objects Automatically
Macro to update the object names
High volume update to join syntax?
row count in universe
Can a universe or new objects be created using API
Access to the Universe Designer SDK
Can I change object-properties for multiple objects @ onnce
How to find the class the object is coming from?
Metadata
SDK / Macro for Loading objects and classes in universe
Extract universe information from CS/file system?
Updating dimension/measure/detail object descriptions
Alphabetized Index to BOB's Downloads
How can I Mass update Objects of my Universe.
Dynamic Object Names
Universe Documentation best practices
Mass Update Object Level Security XI R2
Copying a class and its sub-classes with objects
making new universe in BO using VBA SDK ??!!!
Universe Description Uploads
Mass updating of universe objects in XI
Extract Metadata from Universe
macro code for automatically updating object formats.
Create Class and Objects Automatically
Editing Object/Class descriptions by business users
About copying and changing the object name by prefixing
LOV Properties

Adding a version of the utility for Designer XI. I’ve only tested it with XIr2, so I’m not sure if it will work with XIr1. The only changes are to point to the XI library (Tools, References), and to reflect the syntax change for the logon dialog (.LogonDialog instead of .LoginAs).
UniverseDescriptionsXI.zip (10.0 KB)


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

Dwayne,

This is great. I am trying to modify it, to include the SELECT part from Designer too and it keeps throwing up a “Out of Memory” error. I am using this in XIR3. If the code is tun without any changes then it executes perfectly fine, but when i make the change to include the SELECT it throws up the error.
The changes I made were as follows:
Sub GetInfo()
Range(“Objects”).Resize(Wksht.UsedRange.Rows.Count - 1, 7).Name = “Objects”
Range(“Objects”).Columns(“E:G”).Value = Range(“Objects”).Columns(“B: D”).Value

Sub MakeChanges()
If Obj.Name <> Rng.Cells(RowNum, 5) Then Obj.Name = Rng.Cells(RowNum, 5)
Obj.Description = Rng.Cells(RowNum, 6)
Obj.Select = Rng.Cells(RowNum, 7)

Private Sub GetObjectInfo(Clss, RowNum As Long)
Wksht.Cells(RowNum, 4) = Obj.Select

I would really appreciate if you can let em know where I am erring. Also, it does not return the full list of objects too. I mean, I have close to 290 objects in the Universe and the changed code stops at row 171 ( the original code returns all 290 objects though)


interactive :us: (BOB member since 2005-04-28)

I’ve seen that “out of memory” error reported when the length of the string is > 1024 characters. Try this, and see if the error goes away …

Left(Obj.Select, 1024) = Rng.Cells(RowNum, 7)

Then change it to 1025, and see if the error returns. I don’t have a solution (other than adding a loop to split into “chunks” of 1024 per cell), but at least that will point out the Excel limitation causing the problem.


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

Dwayne,

Sorry for the late response. I tried doing what you asked me to, but that stil did not help. I also tried to look at the length of the string for each object (Select) in the Universe and the max length that a few objects have are 623 charecters. This is a whole lot less that the 1024 that I was trying to put as a restraint.


interactive :us: (BOB member since 2005-04-28)

i tried to run this tool on my system

but got this error
Runtime error ‘430’ class does not support Automation or does not support expected interface

the error is shown in this line of the code mentioned above in post

this may bcoz i have excel 2007 installed on my system

I am working on BO 6.5


nik96159 :india: (BOB member since 2008-11-18)

I believe that is the case. I get the same message, and haven’t had time to research it.


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

Is there a way to get information from the Source Information tab (like Technical Information, Mapping, Lineage) on Objects Properites window?


Dave Sharon (BOB member since 2004-06-02)

hi dave,

were u able to run this tool on your system…as i think this tool is only compatible with excel 2003 not 2007


nik96159 :india: (BOB member since 2008-11-18)

It should work with Excel 2007 as well … at least according to tech support. It’s not working on my workstation with Excel 2007, but it’s probably something to do with my machine.


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

It does not appear those properties have been added to the object model :cry: .


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

@interactive

I saw up in the post that you have been trying to get the Select part also to be updated in the tool.

I just wanted to know if there is any possible way I can apply a mass RTRIM on all the character objects in my Universe to remove blank space.

TIA

Regards
Sid


siddhr007 (BOB member since 2009-05-15)

Hi Dwayne,

did you find any solution for that problem? Im working at a customers company and they restricted access to a lot of things … i get the runtime error 430 as well, with Excel 2007. Any idea what i can try?

Im working with BO XIR2.

Thanks in advance,
Andi


Andreas_Seiler (BOB member since 2009-05-13)

same here i am still not able to use this tool with 2007


nik96159 :india: (BOB member since 2008-11-18)

I did find a solution … got a new computer 8) !!! Kidding of course. I have no idea exactly what was causing the issue. All I know is that it works flawlessly now with Vista / Excel 2007. If you log a tech support case (or whatever they are called now), they can provide you with a “logging” utility that may help them debug your issue.


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

Hi Dwayne,

I am getting the famous 430 error as well.

My visual basic references are ticked for “Business Objects Designer 11.5 Object Library”. I am using Excel 2003 with BO XIR2 SP4. I don’t know what I am doing wrong, If you can help that would be great.

Regards.

Arafiq


arafiq (BOB member since 2006-04-12)

Dwayne,

Great tool! It has saved me 1-2 days of work. I made the changes in object names and their descriptions in 30 minutes (including download of the utility, and reading how to use it).

One suggestion though:
It would be prefect if the utility was capable of renaming class names and class descriptions too :yesnod:

Thanks again.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

What do I do to make this work with XI 3.1 SP2?

Ah no, it’s okay, sorted.


Damocles :uk: (BOB member since 2006-10-05)

As with most of my utilities, they were developed for totally selfish reasons … making my own job easier! That said, glad you also found it useful!

I’m sure that is possible. With all the time you saved before, maybe you tinker with it, and share the results with the class :).


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

OK, done :slight_smile:

The result is attached. I added 1 extra sheet for classes and made few changes and additions to the code.
Universe - Object and Class Names & Descriptions.zip (20.0 KB)


Marek Chladny :slovakia: (BOB member since 2003-11-27)