Execute R script from Excel

volatilityMan · January 5th, 2017, 2:53 pm

Hi

My goal is to omit all the hard work done in R into a simple "click" function in Excel. More precisely, I'm currently trying to make the following:

- Create a button in Excel (VBA?)
- Press the button (Hence run the R script doing the computational analysis)
- Obtain the output in Excel (both estimated values/parameters + graph)

Thus R has done all the work but but it's a black box. The only thing I want to see is Excel and the only thing I want to pres is a button in Excel -- a "user friendly" solution

I've tried with RExcel but it has too much clicking back and forth e.g. mark an area, right click, press run etc. which simply doesn't automatize the desired work.

EDIT: I'm trying to avoid VBA hence the Excel focus.

kermittfrog · January 9th, 2017, 6:53 pm

the quickest - and dirtiest - approach that comes to my mind involves:
- clean output directory using vba (or from within r script?)
- write R scripts that produce csv files and figs, and a file that says what is what and where ("output.txt")
- vba will read output.txt and identify Figures files ("F"), Result files ("R") etc.
- a hidden template will be copied for each result file / figure
- ...
- profit

do you have anything more specific in mind? good luck

Edgey · January 13th, 2017, 11:03 am

Try sticking with RExcel. I think you can control Rexcel through VBA, so that may reduce the clicking.

Otherwise why not ignore excel completely and let R do all the work. Use the libraries XLConnect, r2excel etc. to read and write what you need directly to the spreadsheets.

volatilityMan · January 15th, 2017, 1:56 pm

Thanks for the reply guys.

I went for another solution which is more 'simple' yet does the job:

- Set the R script to be executed every X minute and save the output as csv file.
- Create an button in Excel which simply reads the csv file and plots the output.

kermittfrog · January 16th, 2017, 11:10 am

Thanks for the reply guys.

I went for another solution which is more 'simple' yet does the job:

- Set the R script to be executed every X minute and save the output as csv file.
- Create an button in Excel which simply reads the csv file and plots the output.

Sounds like a good idea as wel.

Execute R script from Excel

Execute R script from Excel

Re: Execute R script from Excel

Re: Execute R script from Excel

Re: Execute R script from Excel

Re: Execute R script from Excel