Export to an excel sheet with a VBA macro

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()

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.

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.

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

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.

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.

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

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.