Fetch data from an open excel sheet into R?

Question

I am wondering is it possible to read an excel file that is currently open, and capture things you manually test into R?

I have an excel file opened (in Windows). In my excel, I have connected to a SSAS cube. And I do some manipulations using PivotTable Fields (like changing columns, rows, and filters) to understand the data. I would like to import some of the results I see in excel into R to create a report. (I mean without manually copy/paste the results into R or saving excel sheets to read them later). Is this a possible thing to do in R?

UPDATE

I was able to find an answer. Thanks to awesome package created by Andri Signorell.

library(DescTools)
fxls<-GetCurrXL()
tttt<-XLGetRange(header=TRUE)

score 2 · Accepted Answer · answered Jun 12 '15 at 16:50

2

I was able to find an answer. Thanks to awesome package created by Andri Signorell.

library(DescTools)
fxls<-GetCurrXL()
tttt<-XLGetRange(header=TRUE)

answered Jun 12 '15 at 16:50

JeanVuda

1,738
14
29

score 1 · Answer 2 · answered May 28 '15 at 23:48

1

Copy the values you are interested in (in a single spread sheet at a time) to clipboard.

Then

dat = read.table('clipboard', header = TRUE, sep = "\t")

answered May 28 '15 at 23:48

Josh W.

1,123
1
10
17

I am looking for a method that doesn't involve manual copying. Is it possible to point R to read results from an excel sheet that is open (results that haven't been saved, in other words, results in memory) – JeanVuda May 28 '15 at 23:52
OK - I interpreted your question as meaning copying and pasting individual values. I'm not aware of anything like that. – Josh W. May 28 '15 at 23:58

score 1 · Answer 3 · answered May 29 '15 at 01:53

1

You can save the final excel spreadsheet as a csv file (comma separated). Then use read.csv("filename") in R and go from there. Alternatively, you can use read.table("filename",sep=",") which is the more general version of read.csv(). For tab separated files, use sep="\t" and so forth.

I will assume this blog post will be useful: http://www.r-bloggers.com/a-million-ways-to-connect-r-and-excel/

In the R console, you can type

?read.table

for more information on the arguments and uses of this function. You can just repeat the same call in R after Excel sheet changes have been saved.

answered May 29 '15 at 01:53

Matthew MacLennan

49
4

I am trying to read an excel in memory file (not a saved one). – JeanVuda May 29 '15 at 02:56
The link I posted contains references to several packages and functions within R that claim to do what you want. RExcel seems like a comprehensive one. – Matthew MacLennan May 29 '15 at 05:09
1

Hi @Matthew, Thank you very much for pointing out to `RExcel`, it is certainly one way to go. I tried to install `RExcel` on my computer. I have Win 8 (64 bit), and Excel 2013. I did install Excel 2007 in order to get `RExcel`. However, couldn't get the things to running yet. – JeanVuda May 30 '15 at 00:37

Fetch data from an open excel sheet into R?

3 Answers3