Automating BO from Access

[rudraigh]

I've looked around and haven't seen what I'm trying to do. I would like to open, refresh and export a report as an Excel spreadsheet from a module in Access. I found a couple of code examples around but, not knowing the BO object model I'm fumbling around quite a bit. Does anybody have any code like this? Or, where can I find the object model and some code samples? I'm using BO 6.5.1 and have my pick of Access versions from 97 on.

Thanks for any help.

 

[skrandel]

What you are trying to do requires a license of the SDK. With 6.5, that may be included. The object model is available on the tech support web site.

Steve Krandel
VERITAS Software

 

[rudraigh]

An SDK license? Bummer. I've automated other progs without an SDK license. You're sure about this?

 

[skrandel]

Yes, I'm sure. You may have automated other packages. Some include an SDK for free, while others charge for it. In the old world, BO charged extra. There is a new pricing model that may include it.

Steve Krandel
VERITAS Software

 

[xlbo]

I can provide some sample code to accomplish this but I would ask why ??

Is there any reason you cannot conenct to the tables from your access database via the ODBC drivers that BOBJ uses and just reproduce the query / data there ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[rudraigh]

Thanks Geoff. I'd really appreciate it.

Why? I work for a gov't entity. We have databases in Oracle, DB2 and Sybase. This agency has the firm belief that BO is the ultimate reporting tool. It took me six weeks to convince them that, in the Sybase case anyway, I could do a lot better with the drivers through Access. However, They're not allowing me the same for the other two DBs. So, for this one report, I use the Sybase driver to grab data from one database but I have to use BO to export a table from the Oracle db that I need for an SSN cross reference. Then I import that table and put together a nice, neat report for the bigwigs. I'm trying to automate as much of this as possible because this is *not* the only report I produce but it takes the lion's share of my time.

Anyway, I'd really appreciate you sharing some sample code with me.

Thanks,
Rud

 

[xlbo]

Ok - this is what I use. There is no command for doing Edit>Copy All so you have to reference the commandbars object. Other than that, it's pretty much the same as for accessing any other app through VBA:

Sub GetBOData()
Dim BoApp As busobj.Application, BODoc As busobj.Document
[COLOR=green]'Create instance of BOBJ[/color]
Set BoApp = CreateObject("BusinessObjects.application")
With BoApp
    [COLOR=green]'log in to BOBJ[/color]
    .LoginAs "username", "password"
    .Visible = True
    [COLOR=green]'open relevant file[/color]
    set BODoc = .Documents.Open ("Path\File Name.rep")
    With BODoc
         [COLOR=green]'refresh data[/color]
         .Refresh
         [COLOR=green]'No function for Edit>CopyAll so reference the commandbar object[/color]
         BoApp.CmdBars(2).Controls("&Edit").Controls(20).Execute
        [COLOR=green]'Activate correct excel sheet[/color]
        Sheets("BOData").Activate
        [COLOR=green]'Paste data[/color]
        ActiveSheet.Paste Destination:=Sheets("BOData").Range("A1")
        [COLOR=green]'Close and save BOBJ report[/color]
        .Close (True)
    End With

End With
Set BoApp = Nothing
Set BODoc = Nothing
End Sub

I don't have a late enough version of BOBJ to be able to export as an excel file but to do so, I believe you will need to change the CopyAll syntax to something like:

With BODoc
     .refresh
     .ExportAsText("File Path\Filename")
End With

This is using the SaveAs textfile method from the SaveAs menu - I would assume that in later versions, it just changes "Text" to "MSExcel" or "Excel"

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[rudraigh]

Far out! Thanks a bunch, Geoff!
And, you're right, the code looks like this:

.ActiveDocument.ActiveReport.ExportAsExcel , sPutPath & sFileName

Thanks and regards,
Rud