SOD with VBA macros

[mprasad]

Hi,

Can any give me the steps to create VBA macro for a SOD file and how to run the SOD file in espreedsheet engine.

Thanks in advance
Prasad

[rmurphy]

e.Spreadsheet has the ability to pass through a VBA macro from an XLS file (note XLSM is not supported) to a new XLS file.

The following DevShare post discusses this.
Including VBA functionality in e.spreadsheet report - Tips & Tricks - BIRT Exchange

You can also read about it in Chapter 13 of the Designing Spreadsheet Reports manual. Here is a link
http://www.birt-exchange.com/documen...et-reports.pdf

Rob

[mprasad]

Hi Rob

Thanks for your reply.

I have created a sod file and sod.xls file with template in same folder.


I opened sod file in espreedsheet designer and previewed it buy clicking run in toolbar and i saved the excel preview in "file-> save view as". i opened the saved XLS file and found macros are not working.

I followed the steps in link and the pdf document(not that much user friendly) but still its not working

could you please suggest me the steps to create XLS with macros and let me know if i have missed anything while creating template

Thanks
Prasad

[averma]

Hi Prasad:
Could you open up designer preferences (in the tools menu) and check what you have in "show report output" in the General tab? It should be set to "As an Excel Workbook in the Designer".

Ashwini

[mprasad]

Hi Ashwini,

thanks for your reply. It is working now in e.spreedsheet designer.

But i included the SOD in my application i cannot get the VBA scripts.

Please find my code below and let me know the changes that i need to do.

 Document doc = null;
	        BookModel book = null;
	        try {
	        	FileInputStream fileInputStream = new FileInputStream("C:\\e.spreadsheet\\VBA\\VBA.sod" );
	        	doc = new Document(null, fileInputStream, new DocumentOpenCallback());
	        	book = BookModel.Factory.create(doc.getBook());
	            book.getLock();	         
	          
	            FileOutputStream fileOutputStream = new FileOutputStream("C:\\VBA.xls",false);
	            doc.fileSaveCopyAs(fileOutputStream, DocumentType.EXCEL_97_REPORT_VIEW, new DocumentSaveCallback());
	            fileOutputStream.close();
	        } catch (Throwable e) {
	            e.printStackTrace();
	        } finally {
	            book.releaseLock();
				try {
					doc.release();
				} catch (IOException e) {
					e.printStackTrace();
				}
	        }

[rmurphy]

Instead of using a FileInputStream, use the Document constructor method that takes File.

Rob

[mprasad]

Finally it is working

thanks Rob and Ashwini for your comment. It really helped me a lot.

The reason i was asking so many doubts is that the none of documents given with espreedsheet was not that much user friendly (for example in Designing Spreadsheet Reports pdf there is no information about show report output which Ashwini suggested).

People who create these document should improve the standard of this document. Since we had thought of dropping actuate products for these reasons.

[mmihaylov]

Hi,

The links that Rob listed in his email explain how to embed VB Macros in your e.SS reports. Once the report is compiled to a report executable: SOX file you execute it programmatically using the e.SE engine API

You don't have to do anything special when executing an SOX file that contains VB macros. The eSS samples and documentation on BE include examples of this.

Please advise if you need more info.

Regards,

Mihail Mihaylov
Product Manager for e.SS

[mprasad]

Hi Mihail,

I went through the links which ROB gave me. But i did not help me to create what i need. The reason is the documents are not clear. Even the guidelines are not good in those documents. For example in Designing Spreadsheet reports using Actuate e.spreadsheet designer(designing-spreadsheet-reports.pdf) document it is said "you save the SOI file
as an Excel XLS file" in page 297 in line number 3. Is it correct are we doing like that for adding macros to templates?

The things worked only when Ashwini told about "show report output" in her reply and i could not find this in any documents.

Also we cannot find proper and helpful document for Using e.ss designer template with e.ss engine.

If you people correct these things it will be helpful for users like us to use Actuate products without using this forum.

Thanks
Prasad