Hello everyone,
I’m working with KNIME and I need to create a “xlsm” file, but the Excel Writer can only create “xlsx” file.
Do you have a solution ?
Kind regards
Clement
Hello everyone,
I’m working with KNIME and I need to create a “xlsm” file, but the Excel Writer can only create “xlsx” file.
Do you have a solution ?
Kind regards
Clement
Hello @clementcastelein -
The Excel Writer node doesn’t currently support writing XLSM files, due to limitations of the underlying Apache POI library. Sorry about that.
well there is a way …
You could use the R package openxlsx to add a sheet to an existing Excel file with a macro. It is not super straightforward since you first have to check if the sheet is there, the delete it, save the file, open it again and then add a worksheet, then populate it and then write the whole thing again. Cf. this entry.
So numerous things could go wrong, but it is possible. Not sure how large the files can get. And I think the R package openxlsx is a little bit picky if you tell it to store an excel file with a macro and then there is no macro. So handle with care.
kn_example_excel_macros_xlsm.knwf (43.0 KB)
Hello,
Thank you for answers. I have found a solution that does not require xlsm files
Best
Clément Castelein
A stupid question, but want to be 100% sure. Is this also the case for “Excel Sheet Appender (XLS)” as it corrupts my .xlsm files?
I believe so. Both of those nodes use the Apache POI underneath the hood. But maybe the workaround @mlauber71 presented above would help?
Yes, it would, but I wanted to make sure I exhausted all my default KNIME options before jumping to R.
Thanks.