How to export at xlsm file

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.

2 Likes

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)

2 Likes

Hello,
Thank you for answers. I have found a solution that does not require xlsm files :wink:
Best
Clément Castelein

2 Likes

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.

2 Likes