Charts in RPE Documents – the Excel OLE way


Expanding the solution described in Charts in RPE Documents – the Excel way it is possible to generate Excel chart and embed them in the RPE output Word document as an OLE object. For PDF a snapshot image of the OLE will be automatically generated by RPE.

rpeactual_ExcelOLEChart

This solution has several distinct advantages over the initial image based approach:

  1. the image scales better with the page size
  2. the chart can be further processed
  3. simpler to code and maintain in RPE
  4. greatly improved progress tracking and error information in the RPE console view

The example template and VB Script are available on the RPE devWorks examples wiki page: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Rational Publishing Engine/page/Examples

NOTE: the VB Script is taken from http://automationlab09.wordpress.com/2010/04/29/how-to-draw-a-chart-in-excel-using-vbscript/  and modified it to fit the process. I do not own the copyright and all the credit goes to the original author.

Solution flow

In terms of implementation the changes are fairly minimal:

  • VB Script – the Workbook is embedded in a Word document as an OLE
  • RPE side – the JavaScript only has to build the command path
  • RPE side – the image element is replaced by an include file element which includes the doc generated by the script. The command caculated in the JavaScript is set in the command” property of the “Include File” element. RPE will run the command itself and route all console messages in the RPE console.

rpeactual_IncludeFileElement

RPE Console Log for running the example.

rpeactual_ConsoleView

Testing the script

As mentioned in the previous article as well you should start by running this script in a command line prompt first. The command line execution is also recommended to test any changes you want to make in the VB Script

c:\>cscript d:\tmp\excelPieChart_OLE.vbs "X" "a|b|c" "1|2|3" "d:\tmp\test.doc"
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

Saving Excel in: C:\Users\IBM_AD~1\AppData\Local\Temp\rad5CB9C.tmp.xlsx
Saving Doc in: d:\tmp\test.doc
Deleting Excel file
All done
Advertisements

Author: Dragos Cojocari

Architect for Rational Publishing Engine

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s