[SOLVED] Lock specific cells in an Excel file from R while preserving formatting

Lock specific cells in an Excel file from R while preserving formatting

I'm trying to lock a block of cells in a series of Excel files, protect each file, and save them in their original location. I have the code working in every way except that locking the columns strips those cells of all formatting. I can't recreate the format manually within the xlsx package because each of the files is slightly different.

I know how to output data into Excel without formatting using XLConnect, but I can't get XLConnect to lock cells/protect workbooks. So I'm either looking for help using XLConnect to lock down the cells, or help using xlsx to lock the cells without overwriting the formatting.

Here is my current code (using xlsx package):

wb <- loadWorkbook(file.path)
sheets <- getSheets(wb)
sh <- sheets[[1]]

lock <- CellStyle(wb, cellProtection = CellProtection(locked = TRUE))
rows <- getRows(sh, rowIndex = 9:50)
cells <- getCells(rows, colIndex = 5:6)

lapply(names(cells), function(ii) setCellStyle(cells[[ii]], lock))
.jcall(sh, "V", "protectSheet", "p@ssword")

saveWorkbook(wb, file.path)

Solution

I think I may have eventually found my own answer by going around xlsx and XLConnect. Instead I wrote a VBA macro:

ActiveSheet.Unprotect ("p@ssword")
Range("E8:F50").Locked = True
ActiveSheet.Protect ("p@ssword")

and then called the macro in R (using RDCOMClient), cycling through the different sheets

xlApp <- COMCreate("Excel.Application")

xlWbk <- xlApp$Workbooks()$Open(paste0(temp.path))
xlApp$Run("LockColumns")
xlWbk$Close(TRUE)
xlApp$Quit()