BusinessObjects Board

macro code for automatically updating object formats.

building semantic layer
#1

Hi,

Is there a macrocode using which the format for object mentioned in excel would be automatically applied to the objects in the universe.

Could any one please help me with this

regards,
Anoop

anoopwins (BOB member since 2009-03-18)

#2

Hi,

What is available is this:


You may find it useful.

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

#3

I have used Dwayne’s Code and modified a little to update the object formats. I have used it for Numeric, Character and Date Objects.

Copy and Paste the following code in an Excel VB Editor and run the macro from it. Make sure that you are referencing the Business Objects 11 Library.

a) Open the Excel.
b) Click Alt + F11
c) Copy and Paste the below Code… (make changes as per your needs)
d) Press Alt+F8 to run the macro.

Used : EXCEL 2003, DESIGNER XIR2

:arrow_forward: Note : This code directly updates the Universe. First test it on a sample universe, next go for the original one.


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 UpdateObjectFormats()

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

    On Error GoTo ErrorHandler:
    
    Set Wksht = ThisWorkbook.Worksheets("Objects")
    Wksht.Unprotect
    Wksht.Cells.ClearContents
  
    Call MakeChanges(Univ.Classes)
    MsgBox ("Changes Updated")
    
    Univ.Save
    DesignerApp.Quit
    Set DesignerApp = Nothing
    Exit Sub
ErrorHandler:
    Univ.Save
    DesignerApp.Quit
    Set DesignerApp = Nothing
    
End Sub

Sub MakeChanges(Clss)

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

For Each Cls In Clss
        For Each Obj In Cls.Objects
            
            If Obj.Type = dsNumericObject Then
                Obj.Format = "0;-0;0;0"
                Obj.Format.Alignment.Horizontal = dsHorizontalCenter
                Obj.Format.Alignment.Vertical = dsVerticalCenter
                Obj.Format.Alignment.RowbyRowAutoFit = True
                Obj.Format.Alignment.TextWrap = True
                Obj.Format.Font.Name = "Arial"
                Obj.Format.Font.Size = "9"
                Obj.Format.NumberFormat = "0;-0;0;0"
            End If
            If Obj.Type = dsCharacterObject Then
               ' Obj.Format = ""
                Obj.Format.Alignment.Horizontal = dsHorizontalLeft
                Obj.Format.Alignment.Vertical = dsVerticalCenter
                Obj.Format.Alignment.RowbyRowAutoFit = True
                Obj.Format.Alignment.TextWrap = True
                Obj.Format.Font.Name = "Arial"
                Obj.Format.Font.Size = "9"
                'Obj.Format.NumberFormat = "0;-0;0;0"
            End If
            If Obj.Type = dsDateObject Then
                Obj.Format = "mm/dd/yyyy"
                Obj.Format.Alignment.Horizontal = dsHorizontalCenter
                Obj.Format.Alignment.Vertical = dsVerticalCenter
                Obj.Format.Alignment.RowbyRowAutoFit = True
                Obj.Format.Alignment.TextWrap = True
                Obj.Format.Font.Name = "Arial"
                Obj.Format.Font.Size = "9"
                Obj.Format.NumberFormat = "mm/dd/yyyy"
            End If
        Next Obj
        If Cls.Classes.Count > 0 Then
            Call MakeChanges(Cls.Classes)
        End If
    Next Cls
End Sub

BO_Chief :us: (BOB member since 2004-06-06)

#4

Hi,

Thanks for the macro code.

But when i run the macro i am getting the following error

Run time error-430 : class doesnot support automation or does not suppor expected interface.

Is there any ways to resolve this issue?

Expecting everyones help on this.

regards,
Anoop

anoopwins (BOB member since 2009-03-18)

#5

@Anoop:

Are you referencing the correct library of Business Objects Designer …?

BO_Chief :us: (BOB member since 2004-06-06)