how to refresh an excel file from within R?

Question

I have some excel file with simple formulas like =SUM(A1:A3).

I need to import the file into R, but before that I need to refresh the formulas. Is there a way to refresh the file from within R? There are good packages for importing the data in a R dataframe (eg. the R xslx package) but I need to refresh my formulas first.

Any suggestions? Thanks!

Answer 1

You should be able to do this with RDCOMClient:

library(RDCOMClient)
ex = COMCreate("Excel.Application")
book = ex$Workbooks()$Open("my_file.xlsx")
book$Worksheets("Sheet1")$Calculate() # if you have many sheets you could loop through them or use apply functions based on their actual names
book$Save()
book$Close()

Here's another thread on the underlying VBA