Is it possible to update universe object definitions from a spreadsheet?

[Former Member]

Is it possible to update universe objects/ definitions from a spreadsheet and if so, how can I accomplish this?

[abdellatif_astito · Accepted solution]

The only way you can achieve this is to use the Designer COM SDK, directly from Excel. The SDK allows you to update all objects in the universe and all their properties, with values from cells of the spreadsheet.

I can provide more details if this is a viable possibility for your needs.

Regards

Abdellatif Astito

[Former Member · Accepted solution]

PLEASE!!! Any help would so wonderful, I have not used the SDK...

Thanks so much!

[abdellatif_astito · Accepted solution]

I have a sample spreadsheet but I don't see a way to attach it to this post.

Anyway, here is basically the approach:

- First you need to add the SDK as a reference to your Excel environement. In Excel 2007 for example, go to Visual basic window then Tools->References. Check the box "BusinessObjects Designer 12.0"

- Add a button to the spreadsheet and associate the VB macro to it. Then write the code of the macro, in the example below the first object found in the first class is updated by the values in cells (13, 4) and (13,5). The first cell updates the name (any string) and the second update the type (integer between 1 and 4). You can do the same with any other property of the object or the table in your universe.

This piece of code performs the entire workflow: starts Designer, pops up login dialog, allows you to open the universe you want to update, updates the objet, and saves and closes the universe.

For more details about the SDK functions you can find the documentation here:

http://www.sdn.sap.com/irj/boc/sdklibrary

Hope this helps

================================

Sub UpdateObject()

Dim DesApp As Designer.Application

Dim Unv As Designer.Universe

Dim MyObject As Designer.Object

Dim Myobjects As Designer.Objects

Dim MyClasses As Designer.Classes

Dim MyClass As Designer.Class

Dim Rng As Excel.Range

Dim name As String

On Error GoTo ErrorHandler

'some Excel related lines

CurrentApp = Application.Caption

Application.Cursor = xlWait

Application.DisplayAlerts = False

Set Rng = Sheets("Sheet1").Cells

Set DesApp = New Designer.Application

DesApp.Window.State = dsMinimized

DesApp.Visible = True

Call DesApp.LogonDialog

Set Unv = DesApp.Universes.Open

Set MyClasses = Unv.Classes

'restore Excel as active application

Call AppActivate(CurrentApp)

Application.ScreenUpdating = True

Set MyClass = MyClasses.Item(1)

Set Myobjects = MyClass.Objects

Set MyObject = MyClass.Objects.Item(1)

MyObject.name = Rng(13, 4)

MyObject.Type = Rng(13, 5)

Unv.Save

Unv.Close

DesApp.Quit

CleanUp:

Set Obj = Nothing

Set Cls = Nothing

Exit Sub

ErrorHandler:

MsgBox Err.Source & " - " & Err.Number & ": " & Err.Description, _

vbCritical, "Failure"

Resume CleanUp

End Sub

[Former Member · Accepted solution]

This is great!

Thank you so much for the feedback... I haven't tried it out yet, however, this really provieds me with a path!!!

[Former Member · Accepted solution]

There are a couple of utilities over at www.forumtopics.com/busobj

Have a look in the Bob's Downloads section.

They will be exactly what you need.

Regards,

Mark

[Former Member · Accepted solution]

Fantastic - got it and was able to get univ objects and descriptions updated same day!

Thanks so much!

[Former Member · Accepted solution]

Hi Michael,

Would you please share which particular download you used from forumtopics website.

Thanks.

[Former Member · Accepted solution]

Hello Thanaus

Shore! Topic label is "Mass update to object names / descriptions" and you can find the VB script here:

http://www.forumtopics.com/busobj/viewtopic.php?t=21029

There are additional comments from users, as far as expanding the current capabilities, however, I found this script saved me at least a day or more updating multiple universe class and object descriptions from a spreadsheet!

Luv BOB!