how to update/refresh an excel file with formulas from within R? (without opening it manually!)

General
1

I have some excel file that contains various formulas. I would like to import the files in R, but before that I need to update their formulas.

Can I use xlsx (or another R package) in Rstudio to refresh the file before importing it? is that possible in R? I would like to avoid opening the file manually if possible.

Thanks!

2

Hi,

You might want to take a look at the Tidyxl package, which has some awesome tools for extracting meta-data from excel files.

https://cran.r-project.org/web/packages/tidyxl/vignettes/tidyxl.html

Hope this helps,
PJ

3

thanks but what I am trying to do is pretty simple actually. I just want to refresh my worksheet before importing it in R. I need to refresh it because the excel file contains formulas.

4

I'm not sure if this would help but the RDCOMClient package allows you to to interact with Excel programmatically, maybe you could use it to trigger an update.

1 Like
5

Hi , the Excel formula will be automatically updated if I put is available, but are you referring to pivot tables or just formulas.

Is there a requirement to add additional rows in Excel prior to import in R

1 Like
6

Could this be done with a call to the system commands, or run a system batch file, that sends a keystroke to the Excel window, like CTRL-S for "save"?

?system
?shell

HTH

1 Like
7

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.