excel pivot refresh

[jpappire]

Hi ALL,

i have a requirement which i want to refresh pivot with values based on other sheet in excel using R, Kindly advise if anyone can help ?

[mara]

@jpappire

i want to refresh pivot with values based on other sheet in excel using R

Could you clarify what you mean by "in excel using R"? Are you using a tool that uses R inside of Excel? See, for example, the descriptions here
https://blog.revolutionanalytics.com/2018/08/how-to-use-r-with-excel.html

[jdlong]

@jpappire

i want to refresh pivot with values based on other sheet in excel using R

So is the pivot in R or in Excel? Can you make a simple example and share with us? It's very hard to guess exactly what you mean. Also, what OS are you desiring this to work with?

[jpappire]

The pivot is in excel and want to refresh after calling in R studio

[jdlong]

I am suspicious that you will need to use the RDCOMClient assuming your R session is also in Windows. I know of no solution that works from *nix.

Here's an example from this StackOverflow question which uses RDCOMClient to call a macro inside Excel. You could create a macro that refreshes your pivot then use this R code to call that macro:

library(RDCOMClient)

# Open a specific workbook in Excel:
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("C:\\Temp\\macro_template.xlsm")

# this line of code might be necessary if you want to see your spreadsheet:
xlApp[['Visible']] <- TRUE 

# Run the macro called "MyMacro":
xlApp$Run("MyMacro")

# Close the workbook and quit the app:
xlWbk$Close(FALSE)
xlApp$Quit()

This example does not have a save step at the end which you may need to add.