Broadcast Agent to Excel

scheduling Broadcast Agent (BCA)
1

Anyone out there know how to have Broadcast Agent send a report to Excel? Could it be done with a script?

Thanks,
Shirley Knight
Business Objects Support at Texas Instruments shkk@ti.com

Listserv Archives (BOB member since 2002-06-25)

2

Shirley

Here is a macro to have all data providers in a report exported to an excel file format and then emailed to a recipient. You can send this to the BCA.

Mike McErlain

Sub ExportExcelFile()
'**************************************************************************** '* Create an excel extract of the data providers for this report *
'* If there is more than one data provider the the files will be sequenced *
'* as DocumentName_n.xls else the file will be named DocumentName.xls *
'****************************************************************************

Dim ThisReport As Document
Dim TargetFile As String
Dim DProviders As DataProviders
Dim CurDP As DataProvider

On Error Resume Next
Set ThisReport = ActiveDocument

'** Refresh the queries
ThisReport.Refresh

Set DProviders = ThisReport.DataProviders

Dim I As Long

For I = 1 To DProviders.Count 'Loop thru all DPs in this doc
Set CurDP = DProviders.Item(I)

'** Set the filename
If DProviders.Count > 1 Then
TargetFile = "C:\BusObj_Files\ConvertToExcel" + ThisReport.Name + “_” + LTrim(Str(I)) + “.xls”
Else
TargetFile = "C:\BusObj_Files\ConvertToExcel" + ThisReport.Name + “.xls”
End If

'** Delete the existing file if it exists Kill (TargetFile)
If Err <> 0 Then
If Err = 53 Then
Err.Clear '** 53=File not found
End If
Else
Call MsgBox("Error on Excel Export: " + Str(Err.Number), vbOKOnly) End If

'** Export the excel file
Call CurDP.ConvertTo(boExpExcel, 0, TargetFile) If Err <> 0 Then
Call MsgBox("Error on Excel Export: " + Str(Err.Number), vbOKOnly) End If

Dim OlkApp As Object 'Outlook Application Dim NewMail As Object 'Mail Item
Set OlkApp = CreateObject(“C:\Program
Files\Netscape\Communicator\Program\sendto32.exe.Application”)
Set NewMail = OlkApp.CreateItem(olMailItem) With NewMail
.To = “Mike_McErlain@ClientSite.com”
.Body = “Attached is your requested excel file” .Subject = “Emailing Excel file from VBA” .Importance = 2
.Attachment = TargetFile
.Send
End With
Next I
End Sub

Date: Sun, 4 Feb 2001 22:54:24 -0600

Anyone out there know how to have Broadcast Agent send a report to Excel? Could it be done with a script?

Thanks,
Shirley Knight
Business Objects Support at Texas Instruments shkk@ti.com

_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com

Listserv Archives (BOB member since 2002-06-25)

3

Thanks Mike, this is great.

Shirley

Listserv Archives (BOB member since 2002-06-25)

4

I am interested in hearing more about this…

I am thinking about writing a VB applicaiton to do this but can you tell me more about how the macro will do via BCA and if this be the case can it be applied to a Lotus notes mail server without a change in the code?

Thanks
Stephen

This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice. Any comments or statements made herein do not necessarily reflect those of J.P. Morgan Chase & Co. Incorporated, its subsidiaries and affiliates.

Listserv Archives (BOB member since 2002-06-25)