BusinessObjects Board

Macros-Multitab report to Excel?

sdk development
#1

Dear BoB’ers

I am new with VBA macros and customizing reports. Here we have a multi-tab report, has four tabs, and we want to convert it into Excel with multiple tabs. How do I achieve this?

Actually we do have a macro here which converts BO documents to Excel, but this is good only when the report has a single tab. This is what the body of this macro looks like;

What do I have to do to make it convert multitab reports. Any help and suggestion in this direction will be greatly appreciated.

Regards
edyl

edyl (BOB member since 2005-10-03)

VBA code to export all report tabs to Excel
Macro exporting PDF and Excel
#2

That macro is exporting data providers, not report tabs. The code to save an entire document (all tabs) as Excel is pretty simple, but not very intuitive:

Call ThisDocument.SaveAs("filename.xls")

It’s the use of the .xls extension that triggers the Excel format.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#3

Hi Dwayne

Thanks a lot for the prompt reply. If possible, would you please show me an example of how to use this. I tried your suggestion but was unable to get it as I am a novice to this area especially VBA programming. I would really appreciate your help. Once again thankyou,

regards
edyl.

edyl (BOB member since 2005-10-03)

#4

If you have searched, you would have found This topic, saving report tabs using VBA

Please go through the entire post.

BO_Chief :us: (BOB member since 2004-06-06)

#5

With all due respect, I’m not sure I can give you another example. It’s only one line of code after all.

Give us more specific detail on what “was unable to get it” means. What steps did you try? Any specific error message? Unexpected result? While you can learn a lot by reading this forum, it works much better when solving specific problems.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#6

Dwayne,

Thanks for your help. I really appreciate it.

edyl

edyl (BOB member since 2005-10-03)

#7

As you suggested, I did the following

Call ThisDocument.SaveAs(“C:/…/…/report.xls”)

It does put an report.xls file in the desired location but when i tried to open it it says

However it works for putting it in .txt format. What do you think is it because of? I am using MSO Excel 2003. Your help would be greatly appreciated.

Thanks
edyl

edyl (BOB member since 2005-10-03)

#8

What version of BusObj are you using? Save as Excel is available in version 6 and higher.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#9

Hi Dwayne,

Thanks again for your reply. Actually we are using BO 5.1.3 (can you believe it?)But anyways I was browsing through the forum and actually found this post:

https://bobj-board.org/t/62901

Like you said Save to xls is only supported in V6 and above, I learnt that simply ActiveDocument.SaveAs (“filename.xls”) would not work for our version. There is a macro listed here for download - which it first turns the .rep to .txt and then activates Excel and turns it to .xls or atleast that is what it looks like. For now i am having trouble to run this macro as i am getting compilation errors

Surely my lack of programming skills in VB is not helping. Anyways worst come worst, i will make each tab into seperate reports and use the macro that converts Data Provider values to Excel.

Thanks again for your help

:wink:

edyl

edyl (BOB member since 2005-10-03)