BM

Hi,

I created a report to export data in Excel. In the Excel file, data are to be formatted or re-treated. This operation can be automated by an event VBA macro.



Is it possible to create a java script which allows to create an Excel sheet with an included VBA macro and how ?

In advance thank you for your help.

by Torsten Haase
Posted on Mon, 11/07/2011 - 11:31

Hi,

it is not possible to create an Excel file with incuded Macro, but it should be possible to create such an Excel file manually, open it in the javascript report, let the report write to the opened excel file and save it as the report result.

To do this you need to

- create an excel file containing your macro



- import the Excel file to the script's category

- in the script use:

 

var xlsData = Context.getFile("template.xls", Constants.LOCATION_SCRIPT)
var xlsWorkbook = Context.createExcelWorkbook(Context.getSelectedFile(), xlsData)

- add your data to the workbook's sheet(s)

- save the workbook using

xlsWorkbook.write()
0
by Bruno MARIA Author
Posted on Tue, 11/08/2011 - 19:11

Hi Torsten,

I just have to apply your solution now, and I am still far to control java script, but I'm not alone on this way.



Thank you very much for your help.

0
by ephem ya
Posted on Thu, 11/08/2012 - 13:19

Hi,

I already try this method because I had to use a Excel template. But now I want to use a Word File.

If I use

var xlsData = Context.getFile("template.xls", Constants.LOCATION_SCRIPT)

and i put an .doc file in the first parameter, in xlsData I have a byte[] of my .doc file. And now, do you know any method who can replace this one

var xlsWorkbook = Context.createExcelWorkbook(Context.getSelectedFile(), xlsData)

for create a Word file using the byte[] given by the 1st method ?

I really want to use a .doc template.

Best Regards.

0
by Torsten Haase
Posted on Thu, 11/08/2012 - 14:16

Hi,



with the latest Service Release of ARIS 7.2, you can use

var wordDocument = Context.getOutputObject ( "youroutputfile.dox", docData)

where docData is the byte array you've read from the template.

On the result object (wordDocument) you can use the methods described in the help for "ExtendedOutputObject" (see ScriptHelp: Methods for reports and semanticchecks -> Objects -> Output object -> ExtendedOutputObject)

Best regards,

Torsten

0
by ephem ya
Posted on Thu, 11/08/2012 - 14:48

Re,

Thank you for your quickly answer and I have the 7.1 , I checked and i don't have this method, she looks very useful for me...

Best regards.

0
by Bruno MARIA Author
Posted on Wed, 12/05/2012 - 11:25

Hi Torsten,

Taking a wysiwyg XL report and opening it with the java code edition mode, I would like to know if it is possible to adapt this code in order to add the VBA macro copied from the template.

I have tried to apply your recommandation in this case but it was not ok : the file generated is either with data without the macro or with the macro without the data.

Here is the structure of the code generated from a wysiwyg report :

 

var oOutput = Context.createOutputObject()
var xlTableActive=false
oOutput.DefineF(getString("ID_STYLE_RD_HEADING_3"), getString("ID_DEFAULT_FONT"), 12, RGB(0,0,0), Constants.C_TRANSPARENT,  Constants.FMT_ITALIC | Constants.FMT_BOLD | Constants.FMT_LEFT| Constants.FMT_VTOP| Constants.FMT_TOCENTRY2 , 0, 0, 1, 1, 0, 1)
etc...
setupOutputObject( oOutput )
oOutput.SetTitle(Context.getScriptInfo(Constants.SCRIPT_NAME))var nLocale = Context.getSelectedLanguage()

createSection1(oOutput, ArisData.getSelectedModels())

oOutput.WriteReport()

function setupOutputObject(outputObj){ outputObj.SetPageWidth(210.10) ... globalFooter(outputObj)}

function createSection1(p_output, p_aModel){.... }

 

Thank you very much for your help.

 

0
by Torsten Haase
Posted on Wed, 12/05/2012 - 12:50

Hi Bruno,

sorry, but I don't quite understand what you want to achieve. If you want to use an excel template instead of starting from an empty excel workbook (is this what you mean by "add VBA macro..."?) you can simply replace "Context.createOutputObject" by "Context.getOutputObject" from the example above.

BR, Torsten

0
by Bruno MARIA Author
Posted on Wed, 12/05/2012 - 13:59

Torsten,

We still have release 7.1 and getOutputObject method is not available... A new reason for us to migrate the Aris platform !

Thanks again for your quick answer.

 

Best regards, Bruno.

0

Featured achievement

Rookie
Say hello to the ARIS Community! Personalize your community experience by following forums or tags, liking a post or uploading a profile picture.
Recent Unlocks

Leaderboard

|
icon-arrow-down icon-arrow-cerulean-left icon-arrow-cerulean-right icon-arrow-down icon-arrow-left icon-arrow-right icon-arrow icon-back icon-close icon-comments icon-correct-answer icon-tick icon-download icon-facebook icon-flag icon-google-plus icon-hamburger icon-in icon-info icon-instagram icon-login-true icon-login icon-mail-notification icon-mail icon-mortarboard icon-newsletter icon-notification icon-pinterest icon-plus icon-rss icon-search icon-share icon-shield icon-snapchat icon-star icon-tutorials icon-twitter icon-universities icon-videos icon-views icon-whatsapp icon-xing icon-youtube icon-jobs icon-heart icon-heart2 aris-express bpm-glossary help-intro help-design Process_Mining_Icon help-publishing help-administration help-dashboarding help-archive help-risk icon-knowledge icon-question icon-events icon-message icon-more icon-pencil forum-icon icon-lock