On a recent project my company decided to use Business Objects as our
primary reporting tool, and for the most part it was the perfect fit. The only
problem was around one specific set of reports; end users insisted that they
should be presented as a multiple tabbed report—one showing current, the other
showing historical information. Although each tab of the Business Objects
report could be saved as separate Excel or HTML files, for example, we could
not find an obvious method to do a direct copy of the Business Objects multiple
tab report into a multiple-tabbed Excel report.

Multiple tabs

We then began to look around on the Internet and within the Business Objects Technical
Support site
for any relevant articles that may help us overcome this
problem and keep our user community happy. After spending a fair amount of time
looking at Business Objects, we came to the realization that the version we
were using would only support a single tabbed report by default, so we began to
look at what other options we had, some of our alternatives included:

  • Creating
    a custom standalone Excel spreadsheet which would have a very complex
    embedded VBA macro that would gather the data and populate the relevant
    tabs when executed via a scheduler.
  • Writing
    an on-demand Web version that could use the Excel ActiveX control on the
    client to populate a two tabbed report (similar to Populate Excel
    Client Side using ActiveX and ASP
    ).
  • Writing
    an on-demand Web version that could use the Excel ActiveX control on the
    server to generate the report and then send it via the browser to the
    user.
  • Write
    an application, e.g., WSH Batch file, VB/Java program that could be
    scheduled to create the files and place them on the server automatically.
  • Tell
    the users that they would have two reports, one for each tab.

Obviously, the latter was not the option of choice, and
given that they had as a company selected to use BO and only BO as their
reporting toolset, a non BO solution would have been very hard to sell. Also,
the browser security model in use and its enforcement removed the possibility
of the browser driven ActiveX approach. Thus, we had to go back to the drawing
board.

We knew that we could write VBA within the Excel shell
provided by Business Objects, so we began to explore methods of using this. We
also realized that we could output each tab separately, and then all we would
need to do is to combine them back into a single Excel file for delivery to the
end users, so we focused our efforts in this area. We came up with the pseudo code in
Listing A for our problem.

We were quickly able to automate a link to Excel and began
looking at how to copy each separate report file into a single Excel instance.
We tried to open each file as a separate workbook using the Workbooks.Open command. However, while
that was fine for the first workbook, it would not work for subsequent ones as
it would always replace the workbook in the Excel file with the one opened last,
disregarding all other workbooks.

We tried seeing if we could copy one of the tabs into memory
using Copy and Paste functionality, then open the other workbook and paste the
data into the correct place.

This looked like something along the lines of
Listing B.

You can see this in action by running the Demo1.xls file
available in the associated download.


Editor’s Note:

To run the downloadable demo Excel files referenced in this article you
will have to turn your Excel macro security setting to low.


However, this did not maintain the formatting we wanted and
was very slow and resource intensive on the server, so we continued to develop
this approach as our fall back while continuing to look for a more appropriate
solution.

Eventually, someone suggested that we use a function
of Excel called QueryTables, a quick
browse through MSDN
suggested that it might do the trick, although it required a database
connection and some SQL to get the data out, which would have meant that we
would have been doing the bulk of the work outside the Business Objects universe.

We then came across another MSDN
link suggesting that a URL could be used instead of the database connection/SQL
approach. As we could natively output each of the tabs in the Business Objects
report as separate HTML files this seemed the ideal approach to take.

We were very quickly able to put together a demo that
allowed us to load as many HTML files into our Excel instance as we wanted to,
using code similar to Listing C.

You can see this in action by running the Demo2.xls file available
in the associated download.

This allowed us to do the import to a new Excel tab for each
file, so we could now load the HTML files into the Excel instance. However ,
the final formatting and layout of the report could not be easily achieved in
this manner, so we decided to create a default report XLT—Excel Template—which
would contain the correct formatting and layout required by our end users. You can view
our update process in Listing D.

The next phase was to interact with Business Objects to
generate each tab of the BO report as a separate HTML file. This again was
quite straightforward using the VBA API of Business Objects to refresh the
report and then export each tab as a separate HTML file as shown in Listing E.

In this way, we were able to loop through each tab in the BO
report and export it to a HTML file with each file in the group retaining a
similar filename, which was to be used in later processing.

Now that we had the data in a new tab in our Excel Workbook,
next to the tabs that were provided by the Template, we simply had to Copy
& Paste the data from these tabs into the correct tab and then delete them.
This was accomplished using the built-in functionality to Copy and Paste a
specified range and then run an autofit to assist in data presentation. We used
code similar to Listing F.

You can see this in action by running the Demo3.xls file available
in the associated download.

Next we decided to use an Excel template that we could
maintain independently of the code; one that could be maintained by either ourselves
or our customer. Demo4.xls shows a simple version of this using the template
Excel file Demo4_template.xls.

However, any cosmetic changes to this template, such as
adding rows, columns, changing the column order etc., would need support from
IT, but simple changes such as changing the background color, fonts etc., could
be done by the customer.

The final piece of the puzzle was the report specific VBA,
which handled calculations, totals and layout of the report, this was run last
before we saved our newly created Excel file.

The final code is shown
Listing G.

Demo5.xls shows a data load into a more complex
template—Demo5_template—and some
custom changes for that specific report. Using this approach, we were able to
very quickly roll out a huge raft of slightly different reports to our customers
by using the standard skeleton based on the QueryTables
and then customize the results as required using the instance_specific subroutine.

As you can see, although BO did not give us the flexibility
that we needed at the time, its provision of an Excel VBA
shell allowed us to interact with Excel in a way none of the developers had
previously used to meet the needs of our customers. The base of this code can
also be reused, as we did, to allow for a manageable way to meet multiple
reporting requirements within your organization without creating a custom macro
for each one.