3

I've found the package XLConnect to be useful for exporting matrices to a CLOSED workbook, but does anyone know how to write to an OPEN workbook?

Alternatively, does anyone know of code one can write in VBA to import a matrix from an R script file?

Thanks

Mike

Mike
  • 1,049
  • 5
  • 20
  • 46
  • 2
    XLConnect _will_ write to an "open" workbook, Excel just won't "auto-refresh" to show you the results. You have to close and re-open it. – joran Sep 30 '13 at 16:08
  • @joran Thanks for the response. I tried that, but it doesn't run. It gives me the following error: `Error: FileNotFoundException (Java): C:\Mike's Work\Book2.xlsx (The process cannot access the file because it is being used by another process)` Also, it appears XLConnect can't write to a ".xlsm" file. Is that correct and, if so, is there another package that can? Thanks! – Mike Oct 01 '13 at 07:15
  • I've tried a slightly different approach now, namely trying to run the R code directly from Excel, VBA using the following code: `RInterface.RRun "source('C:/Mike's Work/R/Guides/Introduction to R Examples/Portfolio Management Sheet.R')"` I keep getting the following error, however: `Error running expression. eval(parse(text=""source('C:/Mike's Work/R/Guides/Introduction to R Examples/Portfolio Management Sheet.R')"))` Does anyone know why that may be? Thanks – Mike Oct 01 '13 at 07:59
  • (1) I only wrote my comment after I had actually tested what I claimed myself. I can, in fact, write to an xlsx file using XLConnect that is open (in Excel) without error. (2) I don't know what an xlsm file is, so I wouldn't know about that. – joran Oct 01 '13 at 14:39

2 Answers2

3

I've been wanting to do just this and stumbled upon excel.link that writes easily into an active excel sheet. The method to write is very simple and straightforward:

library(excel.link)
xlrc[a1] <- seq(1, 10)

Note that inside the brackets you write the cell where the data will be written (if it is a dataframe, this cell will be the upper left of said dataframe).

Result in the active sheet of the active excel file:

enter image description here

David Jorquera
  • 2,046
  • 12
  • 35
2

Use package excel.link this can work with xlsm and also with open excels..

Konrad Krakowiak
  • 12,285
  • 11
  • 58
  • 45
user4644962
  • 21
  • 2
  • 1
    This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – Adrian Cid Almaguer Mar 07 '15 at 19:14