CODEWITHSUNDEEP
rexcelexcel-2010openxlsx
Arthur S. de Lima
Arthur S. de Lima

Reputation: 1

Excel formula not updating after writing new data through R openxlsx package

I'm trying to automate some processes in Excel using R language and openxlsx library.

When I write new data to a cell (which works fine) that is a reference to a formula in other cells, those cells with formulas do not update their values, even after I save the file and try to read it again.

Is there any workaround to fix this behavior?

Code and workbook follow.

library(openxlsx)

getwd()
setwd('Downloads')

wb = loadWorkbook('Plan.xlsx')
wb

activeSheet(wb) = 2
activeSheet(wb)

writeData(wb, sheet=2, startCol = 2, startRow = 2, x = 'p2')
print(read.xlsx(wb, sheet=2, startRow = 2, cols = c(3,4,5), colNames = F))
saveWorkbook(wb, file = 'Plan.xlsx', overwrite = T, returnValue = T)

Plan1 Plan2

Tried to:

  1. save the file;
  2. close it;
  3. read it again;

expecting to have the cells updated with their new values.

Upvotes: 0

Views: 213

Answers (1)

Rainfall.NZ
Rainfall.NZ

Reputation: 336

One way to force a recalculation from Windows is to make a system call to a VBScript that opens, recalculates and saves the spreadsheet. For example, the following saved in a text file called something like: "OpenCalcSave.vbs"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("P:\ath\to\FileOfInterest.xlsx")
objExcel.Calculate
objWorkbook.Save
objExcel.ActiveWorkbook.Close true
Set objExcel = Nothing
Set objWorkbook = Nothing

Then from within R use:

  system("cscript P:\\ath\\to\\VBScript\\OpenCalcSave.vbs", wait = TRUE)

When you next open the file in R, the cells with formulas should all be updated.

The VBScript can be extended to take arguments and to carry out additional functions if required. See here for a more complete description of the solution which inspired this answer.

Upvotes: 0

Related Questions