Icon

kn_​example_​excel_​macros_​xlsm

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

it is possible to use R’s openxlsx to add sheets from within KNIME to an existing Excel file which contains Macros..
With newer Excel Writers this can be done without R.



use R's openxlsx to add a worksheet to an existing Excel file with a macro .xlsmhttps://forum.knime.com/t/how-to-export-at-xlsm-file/13779/3?u=mlauber71 library(openxlsx)# create path for fileexcelFile <- paste0(knime.flow.in[["context.workflow.absolute-path"]], knime.flow.in[["file.separator"]] , "data", knime.flow.in[["file.separator"]] , "data.xlsm" )wb = loadWorkbook(xlsxFile = excelFile)# load existing sheet namessheet_names <- names(wb)# https://github.com/awalker89/openxlsx/issues/256#issuecomment-281380879# define name of new sheetnew_sheet_name <- "data2" #delete sheet if already exists if (any(sheet_names==new_sheet_name)) { removeWorksheet(wb, new_sheet_name) saveWorkbook(wb, excelFile, overwrite=TRUE) wb <- loadWorkbook(file = excelFile) }# add the sheet you defined earlieraddWorksheet(wb, new_sheet_name)# write the data table to the filewriteDataTable(wb, new_sheet_name, knime.in , colNames = TRUE, rowNames = FALSE)# !! save the workbook againsaveWorkbook(wb, file = excelFile, overwrite = TRUE)knime.out <- knime.in Sub Makro1()' ' Makro1 Makro' ' ActiveCell.FormulaR1C1 = "this is a test" Range("A2").SelectEnd Sub add "data3" somtimes does not workto put that intoknime://knime.workflow/data/data.xlsmwould create a new file with a doubleendingdata2/3openxlsxadd sheet "data2"data1from/data/data_original.xlsmdata2append "data4" to data.xlsmdata3data4locate and create/data/ folderwith absolute pathssheet data1sheet data2 Excel Sheet Appender(XLS) (deprecated) Table Creator R Snippet Excel Reader reset "data.xlsm"from original file Excel Reader Excel Writer Excel Reader Excel Reader Collect LocalMetadata Excel Reader Excel Reader use R's openxlsx to add a worksheet to an existing Excel file with a macro .xlsmhttps://forum.knime.com/t/how-to-export-at-xlsm-file/13779/3?u=mlauber71 library(openxlsx)# create path for fileexcelFile <- paste0(knime.flow.in[["context.workflow.absolute-path"]], knime.flow.in[["file.separator"]] , "data", knime.flow.in[["file.separator"]] , "data.xlsm" )wb = loadWorkbook(xlsxFile = excelFile)# load existing sheet namessheet_names <- names(wb)# https://github.com/awalker89/openxlsx/issues/256#issuecomment-281380879# define name of new sheetnew_sheet_name <- "data2" #delete sheet if already exists if (any(sheet_names==new_sheet_name)) { removeWorksheet(wb, new_sheet_name) saveWorkbook(wb, excelFile, overwrite=TRUE) wb <- loadWorkbook(file = excelFile) }# add the sheet you defined earlieraddWorksheet(wb, new_sheet_name)# write the data table to the filewriteDataTable(wb, new_sheet_name, knime.in , colNames = TRUE, rowNames = FALSE)# !! save the workbook againsaveWorkbook(wb, file = excelFile, overwrite = TRUE)knime.out <- knime.in Sub Makro1()' ' Makro1 Makro' ' ActiveCell.FormulaR1C1 = "this is a test" Range("A2").SelectEnd Sub add "data3" somtimes does not workto put that intoknime://knime.workflow/data/data.xlsmwould create a new file with a doubleendingdata2/3openxlsxadd sheet "data2"data1from/data/data_original.xlsmdata2append "data4" to data.xlsmdata3data4locate and create/data/ folderwith absolute pathssheet data1sheet data2Excel Sheet Appender(XLS) (deprecated) Table Creator R Snippet Excel Reader reset "data.xlsm"from original file Excel Reader Excel Writer Excel Reader Excel Reader Collect LocalMetadata Excel Reader Excel Reader

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2019-03-11
On NodePit since: 2019-10-12
Last update: 2024-04-27
Created with KNIME version: v4.7.1
Tags: openxlsxxlsmmacroexcelloadRwritesheet

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!