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.
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,
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
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
