Skip to content
Using the 'excel.link' R extension package to programmatically interact with an Excel file (e.g. read/write cells) from within R
R
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
cluster
mc2d
MonteCarlo.R
MonteCarloV2.R
MonteCarloV3.R
README.md
ReadAndWriteRanges.R
SimpleModel.xlsx
Simple_Example.R
example.xlsx
write1_read1.R

README.md

R_Excel_link

This repository contains code that makes use of the 'excel.link' R extension package to interact with Excel. The intention is to use this approach to programmatically drive Excel models by manipulating data inputs, and then read the results back into R. This will hopefuly enable the use of R's rich feature set to conteract some of the shortfalls of modelling in Excel.

This code requires R and Excel to be installed on the same machine, and the 'excel.link' extension to be intstalled within R (which is PC only).

To install the excel.link package, go the the tools menu from within RStudio, select Install Packages, type 'excel.link' into the 'Package' field of the install wizard and click install.

Files

write1_read1.R - this writes data from R to one cell of the simple Excel model and reads one cell back from Excel to R

ReadAndWriteRanges.R - this writes data from an R dataframe into a named rangle of the simple Excel model where the example models inputs are stored. It then reads the updated results, held within another named range in the from Excel model, back to R.

MonteCarlo.R - this performs a Monte Carlo analysis one a using values within a simple Excel model (SimpleModel.xlsx)

Next Steps

Construct data frames in R that mimic Calculator pathways

Programaatically generate/manipualte the pathway dataframes

Create dataframe to which results from different pathways can be appended

Create charts that show the variation in results between the differnet pathways

Links

http://cran.r-project.org/web/packages/excel.link/index.html

cran.r-project.org/web/packages/excel.link/excel.link.pdf

Aknowledgements

The monte carlo analysis R code used here is borrowed from http://johnpurchase.com/monte-carlo-with-R.html

You can’t perform that action at this time.