1

I have a .xlsm workbook that has a cell B1 that is currently referencing to Cell B2, which has a hardcoded value is 5.

I'd like to use R to remove the reference from cell B1 and replace it with a hardcoded value of 0.

I'm attempting to do this in R by using the xlsx package. However, after using the setCellValue function and opening up the workbook, I see that B1 has a value of 0, but it is still clearly referencing cell B2 (this can be seen in the formula bar as well as the fact that cell B3, which is a function of cell B1, remains unchanged)

Can you help me utilize the xlsx package to overwrite the cell reference from B1 and replace it with a hardcoded value?

Please see below for the code as well as the before and after screenshot of the excel workbook.

Code:

    library('xlsx')

    fn <- 'test.xlsm'
    sn <- 'Sheet1'

    wb <- loadWorkbook(fn)
    sheets <- getSheets(wb)
    sheet <- sheets[[sn]]
    rows <- getRows(sheet)
    cells <- getCells(rows)
    setCellValue(cells$`1.2`, 0)
    saveWorkbook(wb, fn)

Excel Workbook Before Run:

enter image description here

Excel Workbook After Run:

enter image description here

Tyler Law
  • 73
  • 4
  • After running your code, I get the same result. Just noting that if (in excel) you type `CTRL+SHIFT+ALT+F9` it will refresh the formulas and B1 will go back to being 5. This is really odd. – G5W Aug 17 '18 at 00:16
  • I am running your code with xlsx extension and it works... – Nar Aug 17 '18 at 11:52
  • @Nar Hi Nar, so you're saying that when you run it, Cell B1 has a value of 0 and is no longer referencing cell B2? – Tyler Law Aug 20 '18 at 13:30
  • I get the same result as @TylerLaw - value is 0, but references B2. – G5W Aug 27 '18 at 00:45

0 Answers0