R write data into xlsm

Question

I have a data.table: e.g.

> aDT <- data.table(colA = "apple", colB = "banana", colC = "4")
> aDT
    colA   colB colC
1: apple banana    4

I need to write this table into a worksheet in a xlsm (it has macros).

Have tried several ways to connect to the xlsm. All failed.

# Method 1
require(XLConnect)
wb <- loadWorkbook(filename = "sample.xlsm", create = FALSE)
Error: OutOfMemoryError (Java): Java heap space

# Method 2
require(openxlsx)
wb <- loadWorkbook(xlsxFile = "sample.xlsm")
Error in grepl(target, commentsXML) : invalid 'pattern' argument

# Method 3
require(openxlsx)
wb <- loadWorkbook(file = "sample.xlsm")
Error in grepl(target, commentsXML) : invalid 'pattern' argument

I then tried using same codes on another new xlsm, and they worked.

Some issues with my "sample.xlsm" (not sure how they are related to the problem)

1. 20MB
2. take 1-2 min to open it even using Excel
3. The worksheet that I'm going to override is referred to by formulas in other worksheets
4. Every time Excel startup message ".. contains links to one or more ... could be unsafe. If you trust the links, update ..."
5. Have disabled security alerts on the Message Bar

Questions: How to fix it? or any other alternative to write data into xlsm?

I've found some other posts but they are either not working for my problem or being simply outdated. Thanks in advance.

Answer 1

Your file is fairly large, I would probably do more analysis in R and export a more ready-made data to Excel or load data into a database and load the data directly from the database into Excel.

That said, in your method 1 you need to specify how much memory your making available to rJava (which XLConnect depends on) before you load your packages. As so:

options(java.parameters = "-Xmx22000m")

You might not have enough memory available for this but that is the reason your getting this error message.