1

I built a script that pulls from the data warehouse and cleans the data in a format to be export as a csv file. I take the file and paste it into an Excel file that already has a PivotTable with slicers on another tab. The thing is that I am creating a report for every sales rep with their own sales using an Excel macro. There are 300 sales reps that I am sending this unique report to. Right now, I use an Excel VBA macro that creates individual reports and sends them to each sales rep via Outlook which takes a very long time and slows down my laptop.

Is there a way to use R to auto update an Excel file for each sales rep data and refreshes the PivotTable without having to open them individually? I also want to send an automated email which attaches the Excel file and sends to each sales rep via Outlook. I have done it for text and csv files via R but not in a loop manner to over 100 sales rep.

Dmitry Streblechenko
  • 62,942
  • 4
  • 53
  • 78
cs_is_great
  • 33
  • 1
  • 9
  • If you provide an example of your data, and what you want the output to be, I'm confident somebody here can help you do it in R. – BLT Sep 07 '17 at 20:23
  • Within R, you could summarise the data to create the equivalent of a pivot table, write the summary table to an Excel file and email it. This could be looped, using `lapply` or a `for` loop, to create and email a summary table for each sales rep. – eipi10 Sep 07 '17 at 20:43
  • [This SO answer](https://stackoverflow.com/questions/19404270/run-vba-script-from-r) might be helpful in figuring out how to run a VBA script from R. – eipi10 Sep 07 '17 at 20:48
  • @eipi10 The report contains Pivot Slicers to drill down the PivotTable. Is there a R equivalent of it? – cs_is_great Sep 08 '17 at 15:55
  • I'm not sure. I haven't tried to create Pivot Tables or other dynamic Excel objects from within R. Although there are ways to create dynamic content in R using [`Shiny`](https://shiny.rstudio.com/). You could also look at the [`DT` package](https://rstudio.github.io/DT/), which is an R interface to the DataTables Javascript library, and the [`rpivotTable` package](https://github.com/smartinsightsfromdata/rpivotTable), which is an interface to the PivotTable Javascript library. You can use both of these packages with `Shiny` as well. – eipi10 Sep 08 '17 at 18:26
  • I managed to find a solution to updating the PivotTable with fresh data using R. I opened my Excel template with blank data and the PivotTable with slicers. I right clicked on the PivotTable and clicked 'PivotTable Options...'. I went to the data table and checked 'Refresh data when opening the file'. I have to make sure I am using a blank template with a static PivotTable otherwise it will have old data after writing new data. I save the Excel template. I used openxlsx package and uploaded my Excel template into R. I pasted the new data and saved a new workbook. I open the new file. – cs_is_great Sep 11 '17 at 21:49

1 Answers1

1

For sending data from R to Excel, I use excel.link package by Gregory Demin (I believe it is a Windows only package). I found it most useful. https://cran.r-project.org/web/packages/excel.link/excel.link.pdf Here is example workflow on Windows:

Assuming you have a data.frame currently in your r session under object d and you have an Excel workbook saved under c:\mybook.xlsb

# kill an existing Excel process running (if any)
shell("taskkill /im EXCEL.exe /f /t")

# open a workbook from 'c:\\mybook.xlsb'
xl.workbook.open("c:\\mybook.xlsb")
xl.workbook.activate("mybook.xlsb")
mybook = xl.get.excel()


# paste the data from dataframe object `d` to the active sheet in cell (1,1) which corresponds to cell A1
xl.write(d,row.names=FALSE,xl.rng=mybook[["Activesheet"]]$Cells(1,1))

# activate a different sheet named 'mysheet'
xl.sheet.activate("mysheet")

# do more stuff with 'mysheet' here...

# save your workbook to a different copy once done
xl.workbook.save("c:\mybook_current_version".xlsb"))

# kill Excel process again to quit Excel 
shell("taskkill /im EXCEL.exe /f /t")

Check the documentation for more examples.

For sending e-mails with attachments, look at sendmailR package.

Kirill Savine
  • 51
  • 8
  • How do you refresh an Excel file with PivotTables in R after updating the Excel sheet? – cs_is_great Sep 08 '17 at 15:54
  • You can refresh by running a macro command like this: `ActiveWorkbook.RefreshAll`. But you do not need to do that because normally all pivot tables will refresh the next time when user opens the workbook. So if you save the workbook with the new data, close it, and re-open it later, all pivots should be refreshed. – Kirill Savine Sep 09 '17 at 00:05
  • Thank you. I found the option to auto refresh the PivotTable in Excel. I right clicked on the PivotTable and clicked 'PivotTable Options...'. I went to the data table and checked 'Refresh data when opening the file'. I have to make sure I am using a blank template with a static PivotTable otherwise it will have old data after writing the new data. – cs_is_great Sep 11 '17 at 21:30