Preserve macros in xlsm files when processing data with R

Question

Is there a way to preserve the macros in .xlsm files when processing data with R?

I was using the "read_excel" function from "readxl" package to read xlsm data into R, process it, and then export it back to Excel using the "write.xlsx" function in the "openxlsx" package.

However, the write.xlsx function does not allow the output to be an .xlsm file.

I tried an alternative way, using the loadWorkbook and saveWorkbook functions from the "xlsx" package, as suggested in Populating Excel Macro-enabled cells from R/outside environment.

However, this time, my data processing code does not work any more, producing an error, "Error in apply(df, 1, setfunction) : dim(X) must have a positive length".

My code works fine when using the openxlsx and readxl packages, except that all macros were lost in the output.

Is there a way to preserve the macros in .xlsm files when processing data with R?

    df<-loadWorkbook("data.xlsm") # originally was df<read_excel("data.xlsm")

    setfunction <- function (x) {
                set1 <- strsplit(x[[2]], split=",")
                set2 <- strsplit(x[[3]], split=",")
                trimset1 <- sapply(set1[[1]], str_trim)
                trimset2 <- sapply(set2[[1]], str_trim)
                set3 <- setdiff(trimset1, trimset2)
                newset3 <- paste(set3, collapse = ', ')
    }

    df$newcol <- apply(df, 1, setfunction)

    saveWorkbook("data2.xlsm") # originally was write.xlsx(df, file = data2.xlsx")