0

The title to this really says it all. I am trying to automate a report in R, ideally, the Excel file would be imported into R, refreshed (as the said Excel file has a direct link to SQL) before finally outputting the result as an Excel file. Finally, this report needs to automatically run without human intervention or supervision (hence the need for R). Any help or ideas would be appreciated !

AofWessex
  • 157
  • 11
  • I would by-pass the initial read from Excel and perform the SQL query in R, then do your analysis, then write to .csv /xlsx. Also, this question isn't specifically about code/programming, so will likely be closed. – SymbolixAU Apr 26 '18 at 22:39
  • What you said makes perfect sense. However, the authors of the Excel file have devoted a lot of time toward it (a lot of calculated columns that we need are already embedded in said file). – AofWessex Apr 26 '18 at 22:45
  • Sounds like you don't really need R for this? – Tim Williams Apr 26 '18 at 22:48
  • Will `R` be doing anything, or are you just using it as a mechanism to call/refresh an Excel sheet? – SymbolixAU Apr 26 '18 at 22:53
  • R would also need to pivot the data, upload it automatically into SQL and export a Excel file. I know how to do all the aforementioned. However, I am struggling to find how to actually refresh the data in R. – AofWessex Apr 26 '18 at 22:57
  • 1
    so maybe something [like this](https://stackoverflow.com/a/19404499/5977215) is what you're after? e.g, use R to call a 'refresh' macro. – SymbolixAU Apr 26 '18 at 22:58
  • After some extensive research, it looks like I might just add a macro in Excel and then have it run through R. Thank you all for your help. – AofWessex Apr 30 '18 at 15:54

1 Answers1

0

To refresh an Excel Workbook, you can consider the following approach. This approach only works on Windows.

library(RDCOMClient)
path_Excel_File1 <- "D:\\Dropbox\\Reponses_Stackoverflow\\stackoverflow_330\\test_File_1.xlsx"

xlApp <- COMCreate("Excel.Application")
xlWbk1 <- xlApp$Workbooks()$Open(path_Excel_File1)
xlWbk1$RefreshAll()
Emmanuel Hamel
  • 1,769
  • 7
  • 19