Exporting from BO report to Excel via VBA Macro

[DiyaParial]

How can I export data from within a report macro to Excel ? I can do an export to Text or RTF or HTM but I can seem to get it to go to Excel. anyone has any sampe code ?

 

[nainap]

Dim rep As Report
Dim xlapp As Object
Dim ws As Worksheet

Sub test()
Set rep = Application.ActiveDocument.Reports.Item(1)
rep.ExportAsText ("c:\test.xls&quot

End Sub

This shud work

 

[Samdot]

If you want to export the data directly to Excel bypassing the ExportAsText method.

You will have to manually copy each column/cell data to an Excel spreadsheet. To do this you must create an Excel.Application object to copy the data to, loop through each column of data.

Something Like this...
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Set xlApp = CreateObject("Excel.Application&quot
Set xlBook = xlApp.Workbooks.Add

row_offset = 1
row_index = 0
col_offset = 0
col_index = 0

For Each theColumn In boDoc.DataProviders(1).Columns
row_index = 0
col_index = col_index + 1
xlWbk.Sheets(1).Cells(row_offset, col_offset + col_index).Value = theColumn.Name
For i = 1 To theColumn.Count
row_index = row_index + 1
xlWbk.Sheets(1).Cells(row_index + row_offset, col_index).Value = theColumn(row_index)
Next i
Next theColumn

This should copy a table of data from a BO report to an Excel spreadsheet.
I use a variation of this, except I execute the macro from Excel, creating BO application object, opening a report, refresh, copy the data, close BO.

 

[DiyaParial]

Thanks Samdot , that worked well. I used ExportasHtm to write from each tab in the report to Html files, then opened each of these in Excel and copied them over to one Excel WorkBook. I didnt work each cell, I just copied using Workbool.Cells.Select. It worked but in the final Excel file, the formatting of the reports is a little skewed. It copies the firts table just fine, but it shifts then next table to the right by a whole block. Can anyone tell why that should happen ? I havent given an parameters in the ExportasHtml clause.