How to to add VB Excel Script in Excel Generated Jasper Report?

Current Excel Report is created using Jasper Server / Jasper Report 3.8.

In my case , I would like to create Pivot Excel automatically using Excel(Since Jasper Report Crosstab is not sufficient for my client requirement), so i have an idea to add VB Script that will create Pivot Table when the generated jasper report is opened for first time.

I have created the VBA script However I couldn`t find a way to integrate the VBA with Jasper Report (generated) so that when I generated the Report, the VBA is also included in the Report.

Could anyone tell me how to to add VB Excel Script in Excel Generated Jasper Report?

Added Constraint:

List item

The Data Source (Excel File Containing Data) is protected

Solution

I know the question has been answered and accepted, but I just cannot resist to add a thought .....

Could it be that this question is asking to put the cart in front of the horse?

You create an Excel File REP using a report generator. Then you want to use this data as the source of an Excel Pivot table. As I see it there are a couple of possibilities to do that:

Create another Excel file PIV containing a structure of REP with some sample data to create the Pivot definitions
Copy / Paste the REP data replacing the existing content each time you draw a new REP
refresh the Pivot

This can be automated in PIV, asking for the source file name etc., so there is actually no need to touch REP in any way. As an alternative to copy / paste you can just use an external link to REP when you specify the data source for the pivot table, and change this every time the user wants to analyze a new REP.

Hope this helps