I work in BO 6.5 and I have a report that has 30+ tabs. I need to save 20 of these tabs as an excel (.xls) file each month. Is there a way to do this with VB?
I’m pretty new to VB for applications, and know nothing about using VB for Business Objects.
Currently I must manually DELETE all sheets except the ones I want. Then I save the remaining file as the .xls I want. I’m looking for a quicker and easier way to do this.
Any suggestions? Thanks, Dan
DMagnus 
(BOB member since 2005-12-06)
Go to the BOB downloads section
and find the document converter addin. This has code to save tabs to an Excel workbook.
I’m sure it won’t take you long to adapt it to saving specific sheets. 
Mark
robbinma4 
(BOB member since 2005-07-31)
Mark, thanks for the info. I just tried it and it appears to be a nice add in. Unfortunately it does not save the formatting in each report, which is important for me.
Also, like I said, I’m pretty new to VB, so I would need lots of help trying to figure out how to export/convert reports 1-5 into it’s own, single, excel workbook as 5 separate “tabs”.
Imagine deleting all but reports 1-5 in BO, then saving the resulting file as .xls. That’s what I’m trying to figure out how to do programatically vs. manually deleting 20 sheets just to save 5 as excel.
Again, thanks for the info. This AddIn may come in useful in the future…I’m just not sure it’s what will help in this particular circumstance?
DMagnus (BOB member since 2005-12-06)
Sorry I misread your earlier comment about being new to VBA.
I think the trick isn’t to think about deleting the tabs more just exporting the ones that you need.
I had forgotten that the addin does not export the formatting. If you were doing it manually then you could do the Edit->CopyAll triack and Paste it into Excel. I don’t think this can be automated though…
If you want to preserve the formatting then I think you are going to have a problem.
It is possible to saveAsHTML and thus preserve your formatting but the resulting file/directory structure confuses Excel so it just gives a blank page.
Sub copyTest()
Dim myDoc As Document
Set myDoc = Application.ActiveDocument
myDoc.ExportSheetsAsHtml "c:\test\bo_export_html.htm", "Report1"
End Sub
The function does allow you to export specific sheets though using “Report1|Report2”
Maybe someone else has tried this. Have you tried using the search tool to see if there are similar posts?
Someone else may have also developed this but it isn’t something that I have needed to do.
I’ll keep an eye out for anything.
Moderators - could this be moved to the SDK forum please as it is really a VBA question.
Good luck,
Mark
robbinma4 (BOB member since 2005-07-31)
Thanks again, Mark. I’ll keep digging around.
DMagnus (BOB member since 2005-12-06)