I am trying to save 1 tab from a BO document to an existing excel file using SDK. Version is 6.5
Does anyone has a code to do this?
Thanks
Reema
reemagupta (BOB member since 2002-09-18)
Not possible using BusObj functionality. You can create a new workbook using the BusObj SDK, but you’ll have to “fire up” Excel to do the rest. Start Excel, open the two workbooks, and something like this will copy a worksheet from one to the other:
WorkBooks("Book1").Sheets("Sheet1").Copy After:=Workbooks("Book2").Sheets(1)Dwayne Hoffpauir 
(BOB member since 2002-09-19)
Dwayne,
Thanks for your reply.
We tried following code which saves a tab from BO report into an existing excel file. It works in a report run on full client (BO 6.5 SP 2) but does not do anything on BCA version of the same report. It looks as if macro is not doing anything.
Any help is highly appreciated.
Regards
Reema
Private Sub Document_AfterRefresh()
Dim boRep As busobj.Report
Dim strOutputFileName1 As String
Dim strOutputFileName As String
Dim MonthYear As String
Dim boEditPopup As busobj.CmdBarPopup
Dim xlsApp As excel.Application
Set boDoc = ActiveDocument
Set boRep = ActiveReport
Dim doc As busobj.IDocument
Dim strtolist As String
Set rep = ActiveDocument.Reports.Item(2)
ActiveDocument.Reports.Item(1).Activate
Set boEditPopup = busobj.Application.CmdBars(2).Controls("&Edit")
boEditPopup.CmdBar.Controls("Cop&y All").Execute
'boApp.CmdBars(2).Controls.Item("&Edit").Controls.Item("&Copy All").Execute
'boApp.Quit
'Set boApp = Nothing
ThisDocument.Reports.Item(1).ExportAsExcel ("\\hou0advl1\kei\vagovind\BCA\BO Rep.xls")
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = False
xlsApp.Workbooks.Open ("\\hou0advl1\kei\vagovind\BCA\Test Cust.xls")
xlsApp.Workbooks.Open ("\\hou0advl1\kei\vagovind\BCA\BO Rep.xls")
Workbooks("\\hou0advl1\kei\vagovind\BCA\Test Cust.xls").Sheets("Sheet1").Copy After:=Workbooks("\\hou0advl1\kei\vagovind\BCA\BO Rep.xls").Sheets(2)
xlsApp.WorkSheets("Sheet2").Select
With xlsApp.WorkSheets("Sheet2").Range("A1").Activate
xlsApp.ActiveSheet.Paste
xlsApp.ActiveWorkbook.SaveAs ("\\hou0advl1\kei\vagovind\BCA\test_mp.xls")
xlsApp.Quit
End With[/quote]
reemagupta (BOB member since 2002-09-18)
This is going to be a tough one. Debugging on BCA is not easy.
First thing to try is to runit manually while LOGGED ON to the BCA server. If that doesn’t nelp, you can try adding logging to your macro. Write out to a log file after each step. Put the log file in a location on the BCA server that “survives” after the BCA job is done.
Second, I’ve had difficulties using that CopyAll.Execute method … just too fragile … whether interactive or via BCA. It’s one of the methods I eliminated when developing this whitepaper.
Dwayne Hoffpauir (BOB member since 2002-09-19)
Hi,
I am trying to use the above code in XIR2 but getting the following error
Run-time Error ‘9’:
Subscript out of range.
I am getting the same error in some codes i tried.
Please let me know if you need any more additional information regarding this.
Thanks in advance,
Maria_2 (BOB member since 2007-01-17)
Hi,
Can you debug your code and let us know which row is causing this?
Marek Chladny 
(BOB member since 2003-11-27)
Thanks for your responce.
Here it is
Workbooks("\hou0advl1\kei\vagovind\BCA\Test Cust.xls").Sheets(“Sheet1”).Copy After:=Workbooks("\hou0advl1\kei\vagovind\BCA\BO Rep.xls").Sheets(2)
I changed the location at my end accordingly.
P.S This is the same line in every code is creating that error
Thanks Again
Maria_2 (BOB member since 2007-01-17)
Hi,
The error message can be caused by a fact that there are not 2 sheets in the BO Rep.xls file.
Can you verify how many sheets does the file contain?
Marek Chladny (BOB member since 2003-11-27)
Hi,
I have only one tab in BO report.
I don’t understand properly what you are asking.
Thanks for your responces though
Maria_2 (BOB member since 2007-01-17)
So why do you reference sheet 2 in this part of your code?
Workbooks("\\hou0advl1\kei\vagovind\BCA\BO Rep.xls").Sheets(2)
Marek Chladny (BOB member since 2003-11-27)