Document a universe using Excel and the Designer SDK

[system]

Hi Dwayne,

First, Thanks alot.

Below are the exact error messages I get while accessing the file.

To explain, the BobjClient is installed on the Citrix server and the Excel is on my client machine.
Due to security concerns, I am not able to retrieve the file from citrix environment and I am not able to run the macro.

Please input your suggestions, if any other way round is possible.

versions: BO 3.1, Excel 2007

Thank you.

@Dwayne Hoffpauir

@Copper

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.

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

---

Mond (BOB member since 2006-07-31)

[system]

Hi Dwayne,

This tool works like a charm on XI R2, But when I use the same tool on BO XI R3.1 with Office 2007, it’s throwing the similar error that everyone on this chain is pointing.

Compile Error:
Can’t find project or Library.

I see - Business Objects 11.5 Object library shows “MISSING” on the references. I was trying to locate the dll/exe it was pointing to on the XIR2 machine to see which one is missing on the XI R3.1 machine but couldn’t get it due to the limitation on display on the excel reference.

I see the path seems to be changed from
XIR2 -
C:\Program Files\Business Objects\Common\3.5\bin
TO
XIR3 -
C:\Program Files\Business Objects\Business Objects Enterprise 12.0\win32_x86

Is there any work around for this to get it work on XIR3.

Appreciate any help. Please let me know if need any more info.

Thanks again for the wonderful tool…it saves a hell lot of time on many things.

---

suntra (BOB member since 2003-02-06)

[system]

Gurus -

Anyone was able to get through this error to use this utility in XI R3 with Office 2007 ? Appreciate any help.

Thanks in adv.

---

suntra (BOB member since 2003-02-06)

[system]

Hi,

I resolved the issue by installing the Client on my machine and running the macro, it was very easy to capture the details of the universe.

Thanks to Gurus-

@suntra

Gurus -

Anyone was able to get through this error to use this utility in XI R3 with Office 2007 ? Appreciate any help.

Thanks in adv.

---

Mond (BOB member since 2006-07-31)

[system]

Hi Mond,

I do have BO XIR3.1 client installation on my machine but I still get the same error. Any idea?.

Thanks in adv.

---

suntra (BOB member since 2003-02-06)

[system]

Hi Suntra,

First Enable Security Warning to accept the Macro.

Click on “Document a Universe” and it brings up the BO Designer, input the values and browse through the Universe wish to document.

Even though if it throw an error, click multiple times and you will get the details filled in each of the sheet.

Note: check for the macro : Tools > References > Enable the BO 3.1 version.

Thank you

@suntra

Hi Mond,

I do have BO XIR3.1 client installation on my machine but I still get the same error. Any idea?.

Thanks in adv.

---

Mond (BOB member since 2006-07-31)

[system]

@damien4u

I found that the out of memory error is due to the excel limitation of each cell.

1. Max length of cell contents (text) = 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
2. Max length of formula contents = 1,024 characters.

I have a number of object that require to have move than 1024 charactors.
I modified your code to add “’” before the obj.description and obj.select and everything is fine now

Nice catch! Other discussions have pointed to the 1,024 character cell limit, but since it has never impacted me I had not researched the exact reason. Thanks for the research, and a simple solution.

---

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

[system]

@suntra

This tool works like a charm on XI R2, But when I use the same tool on BO XI R3.1 with Office 2007, it’s throwing the similar error that everyone on this chain is pointing.

Compile Error:
Can’t find project or Library.

I see - Business Objects 11.5 Object library shows “MISSING” on the references. I was trying to locate the dll/exe it was pointing to on the XIR2 machine to see which one is missing on the XI R3.1 machine but couldn’t get it due to the limitation on display on the excel reference.

If you included the SDK as part of your client installation, it should be a simple matter of going into the Tools, References dialog box (from the VB Editor), de-selecting the library that is labeled misssing, and then selecting its XI 3.x counterpart from the list … BusinessObjects Designer 12.0 Object Library.

---

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

[system]

Good Morning Folks,
I am in the process of making Data Dictionary based on the existing universe. The excel sheet document has really been helpful to me. The only enhancement I would love to have is to show object universe with their corrsponding table columns. I do not need the select and case statement that comes with coding for object.

So for example:

If universe object is “state ind” and my universe code is "case when state.state_abbreviation = “MN” then 1 else 0 end "…

Here i just want to see :

state_ind => state.state_aabreviation (in the object tab of the sheet)

---

kurt (BOB member since 2006-11-06)

[system]

The only enhancement I would love to have is to show object universe with their corrsponding table columns

Hi kurt,

there is a tool (very similar to Dwaynes one), which has the functionality to report the data lineage.

You can find a brief example here >> http://www.luetzkendorf.eu/download/getsetuniverse/eFashion_v125_20100207_181430.xls
The sheet ‘dependents_eFashion’ contains the lineage analysis for the universe eFashion. For all objects the used tables and columns will be shown. Additionally the objects containing @functions have a reference pointing to the origin objects.

the bob site for the tool >> https://bobj-board.org/t/141886

---

André Lützkendorf (BOB member since 2009-09-28)

[system]

Thank oyu so much for your input. I cannot see the “load Universe” option in the excel sheet. I have enabled the macros.

---

kurt (BOB member since 2006-11-06)

[system]

Thank oyu so much for your input. I cannot see the “load Universe” option in the excel sheet. I have enabled the macros.

Hi Kurt. Please use the topic below to post your questions. thanks
https://bobj-board.org/t/141886

---

André Lützkendorf (BOB member since 2009-09-28)

[system]

@rnola

I have a test environment here where I can compare my results with the test and production universes where I could test by classes,tables, Objects and conditions but not on joins tab. Would working on the macro works ?

If there is a way to get join id’s then that would make this a complete picture.

RNola, it sounds like you are working on a universe comparison tool. How did it work out? Are you willing to share it? I am currently working on the same, but it’s always nice not to have to reinvent a wheel.

In case I do have to finish my own, and for my own edification, can you (or someone) point me to the API documentation? I’m currently trying to figure out how where in the property structure to find the ID’s, such as ObjectID, etc. Thanks.

---

JohnBrooking (BOB member since 2009-12-22)

[system]

@JohnBrooking

In case I do have to finish my own, and for my own edification, can you (or someone) point me to the API documentation? I’m currently trying to figure out how where in the property structure to find the ID’s, such as ObjectID, etc. Thanks.

Answered my own question; SDK is here. However, having to sort things by ID for comparison seems to be more work than simply running Dwayne’s original code (with the addition of getting the ID) and pulling that into a database for querying, so I’ve decided to go that route instead. I’m not as much of a VBA programmer as I used to be.

But any other advice on comparing universes is still appreciated.

---

JohnBrooking (BOB member since 2009-12-22)

[system]

I am getting an issue saying Can’t find project or library. When I try going to the tools menu the references option is greyed out… any ideas?

---

jshaffe (BOB member since 2008-09-19)

[system]

HI,

What abt for Bo Xi 3.1 …its not working there

---

pramod_ps123 (BOB member since 2010-07-21)

[system]

Hi!

I just found this forum when trying to search exporting BO universe into an Excel. The Excel Macro works perfetly for R2 universes. Is there a R3 version available? I need to export my R3 universes to Excel. Thank you so much for your help!

Thanks,
Victoria

---

vshan (BOB member since 2010-07-21)

[system]

Hi,

When attempting to run the macro I’m getting the following error:

“Compile Error: User-defined type not defined” and Sub ListTables(Tbls As Designer.Tables) gets highlighted in the Module1

Please help!!!

---

anuragrathor (BOB member since 2010-10-02)

[system]

All Interested, i had this same error and figure out the solution

Failure in ListObjects()
VBAProject- -2147417851: Automation error
The server threw an exception.

The Errors received are not to do with the VBA, but stem from the default range being restrictive within the objects sheets, meaning an error is returned if large universes are pulled which do not fit. Simply delete the rows e.g. a2;a65536 within the affected sheets. the VBA will then allow the new segment size to fill the abundant space.

i know its an older post, but this is still worthwhile for those searching for this excellent tool, and stumbling during its use.

Regards
gee

---

pyperg (BOB member since 2008-12-31)

[system]

Thanks

It’s very important document. It’s very help to create a universe documents

---

Anis_gul (BOB member since 2010-03-05)