-1

I have a macro-enabled template in excel that I wish to populate programmatically from R.

I have an R process that retrieves different set of data for every item I with to process, processes them, and then I want it to duplicate and populate the template for each data set, bypassing the need to fill the template out by hand for every set of data.

I have been using the XLConnect R package, however it does not support .xlsm files. I thought about using RExcel, but I want to run the program from an R environment, not from within Excel (also I had many difficulties trying to install RExcel).

This is for a course curriculum database validation process that must be done each year, so filling the template out by hand for every single course in my department is simply not feasible and must be done in an automated manner. Part of the reason I want to use R is because I can build a nice user interface with Shiny, where excel workbooks just feel like death.

Is there another way to populate excel macro cells programmatically and actually get the macros to accept and run on the data, or do I need to learn VBA and write a program in excel to handle this?

OmarSands
  • 21
  • 1
  • 4
  • 1
    A search on `[r] xlsm` brings up at least two different postings to SO saying that xlsm files can be read with `xlsx::read.xlsx`. My downvote is for failing to make an effort at searching. – IRTFM Jul 18 '15 at 16:51
  • @BondedDust i didn't search those terms, but I think the OP was also concerned about whether there was a package that would also preserve macros in `xlsm` files. Granted (as the ex below shows) that wld have been super-quick to discover with a small test program (after finding said package that enables reading of `xlsm` files). – hrbrmstr Jul 18 '15 at 19:52
  • `xlsx' and `xlsm` files are actually zipped containers of XML files. So pkg:xlsx probably just opens them up in a temp directory and then re-zips their contents after mucking about in the workbooks section. – IRTFM Jul 18 '15 at 19:58

1 Answers1

3

Something like the following:

library(xlsx)

xl <- loadWorkbook("Workbook1.xlsm")
sheets <- getSheets(xl)
cells <- getCells(getRows(sheets[[1]]))
setCellValue(cells[[1]], 400)
saveWorkbook(xl, "NewWorkbook.xlsm")

will preserve all the macros from Workbook1.xlsm whilst populating actual cells with data.

hrbrmstr
  • 77,368
  • 11
  • 139
  • 205