Can not update xlsm file in the Business Hub workflow

[laughsmile]

Hello everyone

I want to export table data to an existing XLSM file in the Business Hub workflow.
I did it by using “Excel Sheet Appender” node in the KNIME Server Workflow:

But in the Business Hub workflow , it seems there is no “Excel Sheet Appender” node.

So I have to use “Excel Writer” node of Business Hub Workflow.

But when I open the XLSM file which be overwritten by the EXCEL Writer node, the error message “file extension is wrong” appeared.
I have to change the XLSM file extension to XLSX, Otherwise I can not open the file.

How to update xlsm file in the Business Hub workflow?
Can I use the “Excel Sheet Appender” node in the Business Hub workflow like in the KNIME Server Workflow?
Thanks in advance.
Ryu

[mlauber71]

@laughsmile you are using an outdated node. In your screenshot you are overwriting the complete excel file.

You should choose append the file and overwrite the specific sheet and see if that works. Assuming you want to replace a whole sheet.

There are other options involving R or maybe Python but they might be more complicated to be implemented on a business hub.

[laughsmile]

Hello @mlauber71

Thanks for your answer.
I have chosen to append the specific sheet or overwrite the specific sheet,
But after the workflow is executed , the output xlsm file need to be rename from xlsm to xlsx, otherwise the file can not be opened.

Just now I found this:

Ref:

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

So is the only solution to use R or Python script?
Can “Excel Sheet Appender” node be restored ?

Best Regards.
Ryu

[mlauber71]

@laughsmile the (new) Excel Writer is perfectly capable of appending a sheet to an existing excel file with a Macro in it:

Which version of KNIME are you using?

Also the Excel Sheet Appender (XLS) (deprecated) is still very much with us it is just not being initially suggested in the node repository. Though I think it was not able to append XLSM files.

use R's openxlsx to add a worksheet to an existing Excel file with a macro...

Maybe you can post an example (file) or workflow without sensitive data so we could give it a try. Or check this example:

Replace an Excel Sheet in a XLSM File - KNIME Forum (75867) – mlauber71