BusinessObjects Board

VBA to export data from BO report to Excel template

sdk development
#1

Is it possible to write VBA code to automatically export data from a Business Objects report to an Excel

template, then save the template with a new filename? I already have code which exports data from a Microsoft Access table to an Excel template, but I was wondering how I could implement this in Business Objects.

I use the following code to export data from Access to Excel. How would I adapt it so that it works in Business Objects?

Option Compare Database

Sub ExportData()

'*** Export to Excel ***

Dim rs As New Recordset
Dim C As Connection
Set C = CurrentProject.Connection
rs.Open "select * from [Access Database Table]", C

Dim x As New Excel.Application
Dim w As Workbook
Dim s As Worksheet
Dim r As Range
Dim d As String

d = "FILEPATH HERE"

Set w = Workbooks.Open(d & "EXCEL WORKBOOK FILENAME HERE")

'This exports the data to a specific tab and cell in the spreadsheet template

Set s = w.Sheets("Sheet1")
Set r = s.Range("A2")

r.CopyFromRecordset rs

s.Columns("A:F").EntireColumn.AutoFit
s.Columns("A:F").Font.Size = 10

rs.Close
Set rs = Nothing

C.Close
Set C = Nothing

' Saves a copy of the file with today's date included in the filename:
w.SaveAs d & "Final Output - " & Format(Date, "DD MM YYYY"), xlNormal, , , False

w.Close
x.Quit

Set r = Nothing
Set s = Nothing
Set w = Nothing
Set x = Nothing

End Sub

LemonTwist (BOB member since 2008-08-13)

#2

It’s not going to be quite as straightforward. BusObj won’t have a nice clean recordset object like Access. There are a few options, but the approach I have found that works best is discussed here … Using BusObj Reporter/DeskI as data source for Excel/Access

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