VBA : Openning BusObj App and Running Macro

  • Hi Everyone?


    I would like to open an application called Business Objects, it lies in the path “C:\Program Files\Business Objects\BusinessObjects 5.0\BUSOBJ.EXE” and it supports VBA I want to open the program from Excel and run a macro that has been recorded in it. I’ve tried the old traditional way of opening the app then I assign the macro name contained therein but it doesn’t run, I would also like to include a validation check to verify that the app is actually installed in the default path (above) before attempting to launch it. Does anyone have a better way of doing this?


    Ray.

  • Ray,


    I have found the best way to do this is to set up the application you want excel to contol as an object in excel and run the macro you have recorded from within VBA. Make sure in the VBA editor, under tools/references, 'OLE Automation' and if available 'BusinessObjects' or similar is selected. This will alow you to run business objects code from VBA. You can then set up code something like:


    Sub run_BO
    Dim Session1 as Object
    Set BO as CreateObject([here you need the registered application name for business objects - get it from the task manager when it is running])
    Set session1 as BO.[now you're into the code business objects uses, see the help file or macro you've recored but it will be something like 'NewSessionFile' or 'Openfile']
    [set up any other objects the macro you recorded needs to run]


    [set up any attributes you need eg With Session1
    .HOSTNAME = HOSTNAME
    .Port = Port
    End with]


    [continue translating the macro you recorded into VBA]


    [VBA funmctionality eg copy to spreadsheet and process]


    End sub


    More often than not you can copy the data you've extracted onto the clipboard and then past it into a spreadsheet, but watch out Excel can trip over itself here and sometimes you'll have to parse it item by item.


    Good luck!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!