Script for BO report

system · October 15, 2007, 2:12pm

Hi,
I would like to know how can I do to refresh and save in .xls a report every morning with VBA for example without using the BCA… ?
In BO I know that we can use VB.

Thanks.

go_ahead (BOB member since 2007-01-13)

system · October 15, 2007, 3:00pm

What is your reason for not wanting to use BCA

Carl Bell (BOB member since 2007-10-15)

system · October 15, 2007, 3:06pm

My administrator doesn t want to use the BCA…
I’m obliged to found a solution to my pbm…
Somebody could help me please.

Thanks

go_ahead (BOB member since 2007-01-13)

system · October 15, 2007, 4:04pm

Here is a script that may help. You can set the document to refresh on open, but you will have to manually open the report. For OutputDir, type in the UNC location to save the report.

Public Sub Document_AfterRefresh()
Application.BreakOnVBAError = False
Application.Interactive = True

Dim Rpt As Report
Dim OutputDir As String = "\\unc_path\..."
Rpt.ExportAsExcel (OutputDir &amp; Rpt.Name &amp; ".xls")
End Sub

Carl

Carl Bell (BOB member since 2007-10-15)

system · October 18, 2007, 10:33am

Hi,

I would like to know how I can open, refresh and save a report in xls file,
automaticaly by VB please.
The user would like this kind of refresh…

Thanks…

go_ahead (BOB member since 2007-01-13)

system · October 18, 2007, 1:48pm

You could try scheduling a batch script to open BO and call the report with the VBA script. I have never done this before.

Actually, it would be much simplier if your administrator would let you use BCA.

Here is the batch script:
The first line is the directory where BO is installed.
The second line is the report to open, security domain, username, password. the user will have to be granted VBA priviledges in Supervisor.

@echo off
“C:\Program Files\Business Objects\BusinessObjects Enterprise 6\bin\busobj.exe” -online
“C:\ReportName.rep” “Domain” -user “username” -pass “password”

Hope this helps.

Carl

Carl Bell (BOB member since 2007-10-15)

system · October 19, 2007, 7:53am

Carl thank you for your help it works,
But I would like to refresh and save in .xls the report,
how can if I want to group all this fonctionnality in a batch file or vb file ?

Thanks

go_ahead (BOB member since 2007-01-13)

system · October 19, 2007, 1:54pm

Edit and place the VB script (Tools > Macro > Visual Basic Editor) I gave you in the report you want to refresh and export as an .xls
Set the report to Refresh on Open (Tools > Options > Save > Refresh Document When Opening). Use the batch script I gave you to call the report.

Make sure there is no input prompt in the report.

Carl Bell

Carl Bell (BOB member since 2007-10-15)

system · October 21, 2007, 2:54am

[Moderator Note: Moving from BusinessObjects Classic forum to SDK]

Marek Chladny (BOB member since 2003-11-27)

system · October 22, 2007, 7:35am

Hi,
Thank you for the script but I have an another question.
When I launch the script (to open BO), I have to click on “ok” …
I would like to know if there is a possibility in the script to valid the username and pwd without cliking on ok ??

Thanks.

go_ahead (BOB member since 2007-01-13)

system · October 22, 2007, 7:43am

Append this to the batch script for invoking BO

-Nologo -Blind

.

haider (BOB member since 2005-07-18)

system · October 22, 2007, 12:56pm

Hi,

Sorry but I’ve put the macro on VBE, and when I launch the batch file …
It doesn’t create the xls file and doesn’t refresh the report…
How to have an exportation automatically ?

Thanks.

go_ahead (BOB member since 2007-01-13)

system · October 22, 2007, 1:09pm

I replied to this

Paste the final script where the XLS file and refresh is not happening

.

haider (BOB member since 2005-07-18)

system · October 22, 2007, 1:18pm

Delete the previous script and try this one instead:

“C:\Program Files\Business Objects\BusinessObjects Enterprise 6\bin\busobj.exe” -user userID -pass password -online “C:\filename.rep” -keyfile “domain” - nologo

Carl Bell (BOB member since 2007-10-15)

system · October 22, 2007, 2:21pm

For That :

"C:\Program Files\Business Objects\BusinessObjects Enterprise 6\bin\busobj.exe" -user userID -pass password -online "C:\filename.rep" -keyfile "domain" - nologo

=> It works.

The report was opened but, it doesn’t refresh and not save the xls file…

Public Sub Document_AfterRefresh()

ThisDocument.Refresh
ThisDocument.SaveAs ("C:\.....")
End Sub

Why ???
the batch file work, the logon is ok but not the refresh and the save…

thanks…

go_ahead (BOB member since 2007-01-13)

system · October 22, 2007, 2:51pm

This macro did not work?

Public Sub Document_AfterRefresh()
Application.BreakOnVBAError = False
Application.Interactive = True

Dim Rpt As Report
Dim OutputDir As String = “\unc_path…”
Rpt.ExportAsExcel (OutputDir & Rpt.Name & “.xls”)
End Sub

Carl Bell (BOB member since 2007-10-15)

system · October 22, 2007, 3:06pm

Carl no it doesn’t work … ?

go_ahead (BOB member since 2007-01-13)

system · October 22, 2007, 3:19pm

Do I have to add some librairies ?
Or is there an other way to run automatically a macro ?

go_ahead (BOB member since 2007-01-13)

system · October 22, 2007, 3:24pm

In fact it works only when I execute by myself the macro,
but automatically => no … what’s wrong. I’d never use macros on report
help me plz.

go_ahead (BOB member since 2007-01-13)

system · October 22, 2007, 6:31pm

Public Sub Document_AfterRefresh() 

ThisDocument.Refresh 
ThisDocument.SaveAs ("C:\.....") 
End Sub

For the above code to work the document has to refresh on its own when called from batch file.
To do that set the option ‘refresh document when open’ from tools->options->save.
Also remove the thisdocument.refresh, just keep the code for save in XLS.
Remember that when invoking a document from batch program you will have to do an application.quit else the application will be active in the background. So add it too in the code

.

haider (BOB member since 2005-07-18)