BusinessObjects Board

Saving to Excel Using BO 5.1.8

building reports desktopintelligence
#1

I am using BO 5.1.8; I need to save a table to Excel; Export won’t work because I am using multiple data providers. Is there any way to copy and paste into Excel? I tried selecting the entire table then copy and paste into Excel; it didn’t work. Any ideas or confirmation that it is not doable would be great.

Thanks.

Buck :us: (BOB member since 2007-04-10)

#2

Hi,

click on your tab, tehn edit / copy all

Under Excel just paste

Regards

#3

Great. Thanks!

Buck :us: (BOB member since 2007-04-10)

#4

Yes, that’s a good way. Please remember to Search first, as you’d see from Asking Smart BOB Questions, a hyperlink available every time you start a new topic – and to check “sticky topic” FAQs at the top of each forum.

Here’s an entry from the Reporter FAQ: Saving/Exporting to MS Excel.

Anita Craig :us: (BOB member since 2002-06-17)

#5

There is an other workaround to saving a report to Excel.
Save the report as a html file. This will create a folder with a subfolder containing a html file. This file is the table of the report and can be opend with Excel. Just save the file as xls in Excel and done. There might be a little shifting of cells depending on your reports layout.

I even think you could write a macro for it.
folder name = report name (without the .rep)
subfolder name = sheet name in report(I think with spaces replaced by _ )
html filename = sheet name in report (again with spaces replaced by _ )
All this can be found using a macro, even opening Excel and saving the file again.
Surely some wizz can figure it out :mrgreen:

–edit–

Ok, ok, I’m a freak :mrgreen:

Just put this little sub together for exporting to Excel :stuck_out_tongue:

–edit again :wink: –
Added a couple of more line so all reports in the document will be exported
(Oh, and don’t forget to add a refrence to the Excel library for the Excel objects)


Option Explicit
Sub Export2Excel()

Dim strFileName As String
Dim strSheetName As String
Dim objReports As Reports
Dim objReport As Report
Dim strPath
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

strFileName = ActiveDocument.Name
strPath = ActiveDocument.Path & "\"

Set objReports = ActiveDocument.Reports

For Each objReport In objReports

    strSheetName = objReport.Name
    ActiveReport.ExportAsHtml (strPath & strSheetName)
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(strPath & strSheetName & ".htm")
    
    xlBook.SaveAs strPath & strSheetName & ".xls", FileFormat:=xlNormal, ConflictResolution:=xlUserResolution
    xlApp.Quit
    
    Kill strPath & strSheetName & ".htm"

Next

MsgBox "Export Done. ;-)"

Set objReports = Nothing
Set objReport = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
End Sub

It should be standard BO, but what the hell :mrgreen:

SilentBob :belgium: (BOB member since 2006-09-28)

#6

Wow! This is great! I was able to grab the "“FileFormat = xlNormal” and solve a problem I was having. Thanks!

bo_princess (BOB member since 2006-07-25)