CODEWITHSUNDEEP
exceljasper-reportsvba
indrap
indrap

Reputation: 759

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:

The Data Source (Excel File Containing Data) is protected

Upvotes: 4

Views: 1729

Answers (3)

indrap
indrap

Reputation: 759

By looking the answer on Jasper Tracker, seem that this feature is not supported by jasper report and will not be including in new feature

this (Adding VBA Script to Excel Generated Jasper Report) is such a specific Excel feature that I'm pretty sure we will never implement. This is mostly because JasperReports is not an XLS generating library, but rather a reporting tool with Excel output capabilities

See this link for detail.

Upvotes: 0

Alex C
Alex C

Reputation: 17004

I think MikeD has the right idea here. I've never used Jasper, but if it's a CSV or Excel file, why not write a block in your macro script that opens the Jasper report, makes the pivot table then saves it again.

It's honestly been years since I've done VBA but this code was what I found online for opening workbooks (ref: http://p2p.wrox.com/excel-vba/10510-opening-excel-file-vba.html)

 Dim oExcel as Excel.Application
 Dim oWB as Workbook
 Set oExcel = new Excel.Application
 Set oWB = oExcel.Workbooks.Open(<pathToWorkbookHere>)

Forgive the syntax on this as it's from 10 year old memories, but from there, you could do things like

 oWB.Sheets(1).Cells.Copy
 ThisWorkBook.Sheets("PIVOTDATA").Cells.Paste() 
 ThisWorkBook.Sheets("PIVOTTABLE").Cells.Refresh
 ThisWorkBook.Saveas("<path to new report>", xlExcel12)

If your reports are done by date, you could programatically select either the latest, load the folder and parse all of them, or whatever you'd like. Your template file is the one running the script, and saves copies of itself in a "does not contain a macro" state, so your users never get the security warning.

If you correctly determine the filename programatically, set the code to run "ON OPEN" in the template macro, and run a thisworkbook.close at the end of the script, all you've got to do is create a batch file that opens your workbook automatically on the command line

excel.exe <template_filename>

then set a windows cron-job (called a scheduled task) to run automatically every day, right after your jasper report runs.

Sorry if some of this is "hit and miss" in terms of syntax. I've not been elbows deep in Excel VBA for over 10 years, but I wrote a system like this at the time that was generating hundreds of beautifully formatted excel reports every day ... pivot tables and all.

It's totally achievable and you're on the right track.

Upvotes: 1

MikeD
MikeD

Reputation: 8941

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

Upvotes: 2

Related Questions