BusinessObjects Board

Document a universe using Excel and the Designer SDK

downloads
#149

I am still struggling with the error “VBAProject - 430: Class does not support Automation…”. This is triggered by the line Set DesApp = New Designer.Application. I am using Excel 2007 on Vista. I also have Visual Studio 2005 installed with .NET Framework v3.5. I have local admin rights on my pc.

I have the correct references set under Tools - References. Intellisense works in the code and from the Object Browser I can see Application as a class in the Designer library.

I have tried the resolution in Solution to VBA error 430 Class does not support automation with no success.

It seems like Excel cannot connect to the correct dll to launch Designer at runtime. Any thoughts on what else I can check ?

margarita (BOB member since 2005-11-08)

#150

Check whether anyone in this thread has got the code working on Vista and/or Excel 2007.

Nick Daniels :uk: (BOB member since 2002-08-15)

#151

Thanks for the constructive comments Nick :wink:

I appreciate this macro was designed and tested on a previous OS and Excel version. I’m sure I can get this to work, and the documentation that it provides is too valuable to me to just give up on it.

margarita (BOB member since 2005-11-08)

#152

This is great - We have been talking about possibly building a SQL knowledge base from the columns in the spreadseet and accessing that information via a universe. I looked for the universe name to be imbedded somewhere in the spreadsheet but didn’t see anything. Is the name and business objects folder available anywhere in the actual spreadsheet?

Thanks
Rich

EIC_Rich (BOB member since 2008-12-17)

#153

No, and it’s so frustrating. I know that it DOES work with Vista / Excel 2007. I got a shiny new computer a few months back, and it works flawlessly, but I can’t explain why.

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

#154

I got it eventually :smiley:

For me it was a combination of :

  • local admin rights to pc
  • explicitly set Designer.exe to run as Administrator
  • turn off Vista User Account Control (UAC)
  • added config file to directory containing Excel.exe to point to correct .NET Framework. I haven’t experimented removing this file to see if this makes a difference.

It makes it difficult if you work in a locked down environment. I have to change the UAC setting every day that I want to run the macro as it resets each night :roll_eyes:

margarita (BOB member since 2005-11-08)

#155

Good stuff! Hopefully we can get a few more folks to confirm if these steps help, because it’s been quite the mystery for some time!

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

#156

wow!!! :+1:
amazing tool. Thanks a lot.

I am trying to find the Class to which the object belongs? The RootClass function returns the name of the root class. How can i get the name of the immediate Class and not the root class?

If this Question has already been answered in this huge thread can u please guide me to that post.

Thanks in advance

srijoy (BOB member since 2009-09-29)

#157

Hi,

How can the script be ajusted so it will fetch all the universes info ?

Yoav

yohab (BOB member since 2003-12-24)

#158

Not sure I follow the question exactly. The .RootClass property returns the “parent” class of the given object. If you are looking for all of the intervening classes above it (the class “folder” structure), I can see two choices. First, you “sort of” have this information on the Classes tab of the utility. Second, you could tweak the code to successively concatenate a string with the “trail” of classes … say, inserting a “/” between each level.

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

#159

Hey Dwayne,

i was actually asking if it’s possible to document all the universes files rather than just one at a time.

Thanks

Yoav

yohab (BOB member since 2003-12-24)

#160

He was answering the post prior to yours.

It doesn’t make sense, in my opinion, to do everything at once. If you wanted to, I am sure you could put a “wrapper” loop around the base code provided in the utility and loop through all of the universes. You would need to figure out how to store the information.

Dave Rathbun :us: (BOB member since 2002-06-06)

#161

I considered including that feature in the utility, but came to the same conclusion as Dave. It just didn’t make sense to me. Most users of the information would immediately put in a filter to look at one universe at a time. That said, if you wanted to document all universes stored locally, you could adapt the “recursion” technique shown in this utility, and apply it to *.unv files.

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

#162

I too had difficulty in getting this to work for BOXI 3.1 on Vista, but thanks to Margarita’s tips I got it working with:

  1. Developer i.e local admin rights on Vista
  2. Setting the Designer.exe to run as Administrator
  3. Turned off User Account Control
    (FWIW I didn’t need to do the last step involving a config file.)
    Thanks to Dwayne & Margarita

rodlangham :uk: (BOB member since 2004-04-13)

#163

Thanks for this great tool Dwayne! :+1:

I was wondering if there is a way to get the connection information, such as Connection Name, Database, and User, using your macro.

Please let me know.

Thanks!

rbrito :ecuador: (BOB member since 2007-09-06)

#164

Sure, give these a try:

DesApp.Connections(Univ.Connection).Name
DesApp.Connections(Univ.Connection).DatabaseEngine
DesApp.Connections(Univ.Connection).DatabaseSource
DesApp.Connections(Univ.Connection).UserName

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

#165

Dwaine, this is an absolutely fantastic utility - ive been using it for quite a while now and its saved me significant abouts of time.

I have been trying to modify some of the code slightly with limited success. I basically want to change the Table Name(s) column on the Objects tab, so that rather than a list of the distinct tables used in each object, it displays a distinct list of the table names & column names… (ie - tbale1.column1, table 1.column2, table2.column1 etc)

We have a number of complicated objects in one of our Universes with multiple table/column references (decodes/case etc).

The code is:

For Each Tbl In Obj.Tables
Rng(RowNum, 7) = Rng(RowNum, 7) & Tbl.Name & ", "
Next Tbl
'… but remove the trailing comma
If Obj.Tables.Count > 0 Then
Rng(RowNum, 7) = Left(Rng(RowNum, 7), Len(Rng(RowNum, 7)) - 2)
End If

I have tried modifying it, including ‘Dim Col As Designer.Column’ at the start - but I keep getting compiler/compatability error’s.

Do you think this is possible, and (if so), would you be able to point me in the right direction please??

mrsnow (BOB member since 2006-11-16)

#166

The only way I can think of is to parse through the .Select property (the SQL) for the object … nasty.

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

#167

is there anyway to get the unqiue object ID of any of the measurements/dimensions?

AuguC (BOB member since 2009-11-20)

#168

Please see this post back on page 3 of the topic. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)