excel pivot refresh

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 ?

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

1 Like

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?

1 Like

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

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.

1 Like

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.