BusinessObjects Board

VBA to save 3 different full-client BO Reports in 1 Excel

sdk development
#1

Hi,

Once again i am opening this case as i didn’t get any solution.here the problem is i am having 3 different full client reports.i need a VBA macro to save theese three in one excel when i refresh the reports.for example i am having 1st report 10 rows, 2nd report 20 rows and 3rd report 30 rows these 3 reports are different names like Bank.rpt,Bank1.rpt,bank3.rpt when i refresh these reports,these have to save in one excel file at one place that is XXX.Xls and it has the total 60 rows. can it be done? i’ll appriciate for any clue or code.Thanks

Sree

sreeraja (BOB member since 2004-07-21)

#2

Can it be done? Yes. :slight_smile:

You’ll need to start an Excel session in the background, and load / save each of the 3 files into a single Excel workbook. There are samples in this forum that show you how to start and manage an Excel application… look also in the BOB’s Downloads area.

Dave Rathbun :us: (BOB member since 2002-06-06)

#3

Thanks dave for your reply. I searched every where but i did’nt find solution.actually I have seen that they are exporting one report containing many reports(different tabs) into one excel but i didn’t find different reports to one excel.i am here giving my code to export one report in to excel.if i run second report with the same code its over writing the Excel. see my code and give any suggetions.

Private Sub Document_AfterRefresh()
Dim ExcelApp As Object
Dim doc As Document
Dim rep As Report
Dim HtmlFile As String
Dim Path As String
Dim ExcelFile As String

Set doc = ActiveDocument
Set rep = doc.Reports(1)

Path = “C:\XXX”
HtmlFile = Path & rep.Name & “.htm”

Call rep.ExportAsHtml(HtmlFile, 1, 1, 1, 1, 1, 1, 0, 0)

Set ExcelApp = CreateObject(“Excel.Application”)
ExcelApp.Visible = False
ExcelApp.Interactive = False
ExcelApp.Workbooks.Open (HtmlFile)
ExcelApp.Cells.Select
ExcelApp.Cells.EntireColumn.Autofit

ExcelFile = Path & rep.Name & “xls” & “.xls”

On Error Resume Next
Kill ExcelFile

ExcelApp.ActiveWorkbook.SaveAs (ExcelFile)

On Error Resume Next
Kill HtmlFile

ExcelApp.Quit
Set ExcelApp = Nothing

End Sub

sreeraja (BOB member since 2004-07-21)

#4

Look at this code posted in BOB’s Downloads. It exports individual report tabs to text files, then imports those text files into a single Excel workbook. While the code may not be exactly the same, you might be able to use the same program flow.

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

Here is the excel part use the BO to excel code found on BOB. Added open filename andsheet.add before you paste. Filename is the complete path.
exp:
dim ex as Excel.application
dim exbook as Workbook
set exbook = ex.workbook.Open(fileName)
exbook.sheets.add
exbook.activesheet.paste

dvraggs :us: (BOB member since 2003-10-13)