Document a universe using Excel and the Designer SDK

downloads
121

Hi…
I am facing the error,

VBAProject- -2147417851: Automation error
The server threw an exception.

after this i am getting documented information, however its incomplete universe details. like no data in context tab, less objects in objects tab.

if anyone could upload the latest modified EXCEL, that would be great help for us …

Vills :india: (BOB member since 2007-10-24)

122

While this error has been reported a number of times, the great majority of the 15,000 or so downloads have no issue. I’ve not been able to isolate the issue, but it must have to do with workstation configuration (Excel version, BusObj version, order of install, other conflicts). I’m guessing if you had a clean workstation build plus a clean BusObj client install, it would work just fine.

If anyone can find a trend, when it broke, what software combinations work versus don’t, I’ll be glad to try to research it.

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

123

Hi Dwayne !

first, thanks for your ‘document’ Excel tool. It’s very useful.

But I’m facing a strange error :

I’m using your tool on a XI R3.1 universe, from Excel 2003 SP3. I have made the changes you indicate about library (12.0) and almost all work correctly except the ListContext() function.

In the ListContext() function :

For Each Cont In Conts
For Each Jn In Cont.Joins

when the macro wants to run those steps, an error occured on the second ‘For’ line. The error message is “VBAProject – 2147417851 : Error automation…”

For more precision, I tried, as you suggested to Ramprasad on the first page of this topic, to replace “Rng(RowNum, 2) = Jn.Expression” by “Rng(RowNum, 2) = Left(Jn.Expression, 1024)” but problem persists.

I ran this tool with another universe having 2 contexts with small joins and it works. May be, as you suggested too, error occured when joins are too long and the designer.joins dimension is too small.
I ran this tool on the universe which occured this error and I delete almost all contexts : no error. I think that some contexts or joins in context are not accepted by the designer.joins dimension.
How can I modify my macro to accept long joins ?

Could you help me ?
Have you some ideas about this bug ?

Thanks by advance for your help.

Ben

bhch :fr: (BOB member since 2007-12-31)

124

Hi again !

After some other tests, I think I can explain why we can face this error :

VBAProject- -2147417851: Automation error
The server threw an exception.

In my case, I was facing this error in contexts macros.
I have identified, for each context, which joins has generate the error by using this code :

For each join generating the error, I have opened the context and deleted the join flagged to ‘true’ and the next one).
I have saved my univers and, in the same context, I have added the joins deleted before.

After saving again, problem is solved : no more error.

I hope it will help :wink:

bhch :fr: (BOB member since 2007-12-31)

125

Thanks so much for this utility. I worked perfectly for me and has saved our PM folks a ton of time already.

Thanks Again!

Shane

ShaneMorris (BOB member since 2009-05-05)

126

You are most welcome.

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

127

Dwayne,

I looked at the OMD and can’t tell if what I want to do is available or not.

I’ve got a class of LOVs. The LOV properties have a “List Name”. I’d like to be able to pull out the List Name as well as the sql that is returned by the lov.

Is it possible?

Eileen King :us: (BOB member since 2002-07-10)

128

I don’t think so. The .Name property will work, but a warning that “touching” that property actually causes the LOV to REFRESH, so even that will be VERY slow. I don’t believe there is any way to get to the LOV SQL via the SDK.

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

129

Thanks anyhow! I just wanted to make sure I wasn’t missing something.

:mrgreen:

Eileen King :us: (BOB member since 2002-07-10)

130

I’m trying to run the Excel VBA application from a remote server rather than install the MS Office software on the BusinessObjects server. When I press the button to run the script I receive the message “Can’t find project or library”.

Then when I go into Tools->References to specify the correct library I see “Missing: BusinessObjects Designer 11.5 Object Libary”, but the full pathname and filename cannot be displayed by the GUI (too long).

Since I am using BOXI3.1, can anyone point me to the correct dll path and filename to register in order to utilize this universe documentation utility remotely?

Appreciate your help.

Copper (BOB member since 2005-11-10)

131

Did you install the BusObj client software on “whatever” machine has Office? The SDK (and references accordingly) would be built automatically from there.

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

132

In BOXI 3.1 , Context.Joins seems corrupted. The array containing Joins seems to have corrupted data. Cells can’t even be tested to check if they’re null…

The solution proposed by bhch seems the only way…

edit : I was wrong. Corrupted entries in the array are not always at the beginning.

Sub ListContexts(Conts As Designer.Contexts)
    
    Dim Cont As Designer.Context
    Dim Jn As Designer.Join
    Dim Rng As Excel.Range
    Dim RowNum As Long
    Dim i As Integer
    
        
    On Error GoTo ErrorHandler


    Application.StatusBar = "Documenting contexts..."
    Set Rng = Sheets("Contexts").Cells
    RowNum = 1
    For Each Cont In Conts
        For i = 1 To Cont.Joins.Count
        
            RowNum = RowNum + 1
            Rng(RowNum, 1) = Cont.Name
            Rng(RowNum, 2) = Cont.Joins.Item(i).Expression
            Rng(RowNum, 3) = Cont.Description
        Next i
    Next Cont

CleanUp:
    Set Cont = Nothing
    Set Jn = Nothing
    Exit Sub

ErrorHandler:
If Err.Number <> 0 Then

'Rng(RowNum, 4) = "True"
'MsgBox Err.Source &amp; " - " &amp; Err.Number &amp; ": " &amp; Err.Description, _
' vbCritical, "Failure in Listcontexts()"
Err.Clear
Resume Next
' Resume CleanUp
End If

works fine like this for me. In the end, the only thing to do is to filter on the sheet for not null join expressions. No need to delete joins on universe.

It’s like bhch code for error handling which allows to keep on scanning contexts. ( i’m too lazy to try with the initial for each and i don’t if it’d work )

toj (BOB member since 2009-02-10)

133

toj :

my code only allows to know wich contexts are corrupted. The code will not stop on the first error and generating a error message. On each error, the macro continue on the next context.

So I know which contexts, on which join, are corrupted and I solved the problem by delete the join from the context, and insert it again.
But sometime, there can be several joins corrupted in a context and I have to repeat those steps …

May be this code can be improved…

bhch :fr: (BOB member since 2007-12-31)

134

Hi,

I tried to run macro but got error: Can’t find project or library. I’m using XIR3.1 and macro is XIR2 version.

My question is:

My references otions under tools is NOT ACTIVE. Can you please give me idea how can I can make Tools> References option active? Thanks.

moons :us: (BOB member since 2007-11-08)

135

Dwayne / Bo cheif,

I’ve tried running the code however it seems to get stuck in the definitions itself and throws out the following error

The moment the code runs, the error messg pops up. Ive even disabled all other parameters since I only need the classes and their parent classes. The error shows up before and after the modifications.

Run-time error '430' Class does not support automation or does not support expected interface.

Has anyone else encountered this error and dealt with it somehow.

I have BO Designer Objects Library 12.0 activated in the references.

Thanks
Ash

ash_for_bo (BOB member since 2005-08-29)

136

Hello,

I have following components installed in my machine -
windows vista
bo designer 6.5 , bo objects, bo supervisor,uds designer 6.5
excel 2003.
I get the error -
Run-time error ‘430’ Class does not support automation or does not support expected interface at line
Set DesApp = New Designer.Application

I have proper ref selected in vba for designer 6.5

My question is -
is my installation only client thats why i am getting this error. Or its because of different reason.
I have seen this questions asked several time, can anybody suggest whats the cause of this error.
regards
rajesh

bhardwaj_rajesh (BOB member since 2009-07-17)

137

Here’s something I came across after a bit of search for the solution which I believe should help. I havn’t really tried it since the setup at my workplace is really restrictive.

Hope this helps everyone facing the Run-time error ‘430’ error.

Thanks

Ash

ash_for_bo (BOB member since 2005-08-29)

138

Hi all,

i’m new in this monster Thread ;o) and have one question. is there an existing working version from the document a universe for XI 3.1 and Excel 2007.

This tool is very helpful and thans to all which are working on that.

best regards
Hannes

rpri292 (BOB member since 2006-02-17)

139

I am hooked on this tool. THANKS!! But… I’ve upgraded to 3.1, and found that it no longer works. I updated references to the Business Objects 12.0 Object Library, but it looks like more code changes will be required.

For now, I think I’ll re-install the old Designer on a different PC so I can continue to use this awesome tool.

I look forward to hearing that a VBA guru has created a new and improved version.

Thanks in advance!!

BOB_Rocks (BOB member since 2008-12-17)

140

Love the spreadsheet, if you need to document number format for the objects then add the following line to the Sub ListObjects:-

Rng(RowNum, 21) = Obj.Format.NumberFormat

after the line

Rng(RowNum, 20) = Obj.Show

Lakeuk (BOB member since 2004-05-13)