gdemin / excel.link Goto Github PK

Hi,

great package. I know a lot of people that might try R if it was integrated into Excel.

An issue I have is that currently the output of xl.connect.table don't really behave like other R objects and I wanted to ask if you had looked into using active bindings instead:

DT<-data.frame(x=1:10, y=2:11)

makeActiveBinding(
  "DF",
  function(.new_value=NULL)
  {
    if (is.null(.new_value)) {
      return(DT)
    } else {
      DT<<-.new_value
    }
  },
  parent.frame())

DF
DF[1,1]<-400
DT

This means that the value of xl.connect.table can't simply be assigned to a variable but you could create something akin to pryr's %<a-% operator:

DF %<xl-% a1
DF %<xlc-% a1:b24

Thx
Stefan

If I cause excel to pop up a question dialog box, excel.link calls hang until I dismiss that excel window.

Would it be possible to add a timeout to these calls? I know I'd have to catch the error, but that's better than a hung connection.

(I'm using excel & rserve for remote access to data in excel workbooks. so there may not be anyone at the screen to click on the dialog box.)

Hi;
I am going through the excel.link pdf right now and have a question. I found a way to delete an existing excel sheet but can't find a way to delete a range of data within the spreadsheet. Say I want to delete old data from A1:C8 from the active sheet and then re-populate with most recent data. How can I accomplish that?
Thanks for the great package by the way.

I have a script that opens a workbook, reads a range, runs a macro, then closes the workbook. The script works fine, but when I go to open the workbook in the Excel Application, none of the worksheets or worksheet tabs are visible. I know the workbook is loaded because the range name dropdown list in the toolbar shows the range names. If I try to open some other similar workbook I have, the worksheets are not visible. Based on some web searches I have discovered if I load an excel add-in (e.g. Solver), the worksheets become visible. Interestingly, I have also found that if I open R_connection_examples.xlsm before I open my workbook, all worksheets are visible.

Any ideas what could be going on? Is there a workaround I can automate in the workbook?

Thanks.

When trying to load an excel file with a time (HH:MM:SS) columns, this is automatically reformatted into fractions of a year

e.g. 06:48:00 -> 0.283333

When using Read.CSV, one can specify StringAsFactors=F to ensure all columns are treated as string and remain unaltered... is there any such option we can use here?

Hi,

Wanted to try out your package but get warning:
package ‘excel.link’ is not available (for R version 3.5.2)

Do you need to update the package or can I somehow work around this?

Is there a way to set multiple password arguments for xl.read.file?

Is there a way for xl.workbooks() to return the full path to the open workbooks?

Hi,

How can I see the logs of the R script that is run?

I have a R script that writes something to Excel, then the script ends. When I afterwards close Excel, Excel does not shut down completely, but stays as a hidden instance in Task-Manager.
Steps to reproduce:
Open empty Excel file "Mappe1.xlsx"
Run script:

library(excel.link)
xl.workbook.activate("Mappe1.xlsx")
xl["a2"] <- 1
xl["c1"] <- 2

Close (all open) Excel files.
In Taskmanager, the instance of Excel is still running.

Is there a possibility to detach the COM connection?
Would be great if there is a solution - and thank you very much for the package!

Wolf

Using Excel 2013, I noticed xl.workbook.add() creates two separate books instead of one if excel is not already running when the function is called.

Specifically, when the function is called it executes ex = xl.get.excel(), which starts the excel process and opens "Book1" if excel is not running. If it is running, another workbook is created. Then when ex[['Workbooks']]$Add() executes it creates yet another workbook. The same behavior happens with xl.workbook.open() and also seems to be due to the xl.get.excel() function creating it's own workbook when executed, then the rest of the function appropriately opening the file you specify.

It's this line right here at the end of the xl.get.excel() function causing the duplication:

if (xls[['workbooks']][['count']] == 0) xls[['workbooks']]$add()

I could be wrong but in cases where we are calling workbook.add() or open() without excel running, this line isn't necessary as the rest of those functions will create the initial workbook.

Great package though, thanks!

In Poland I am using US version of Excel with Polish locales. All excel formula parameters are separated with ";" and all decimal points are ","
It is probably why UDF RExec fails to execute throwing "Error in R script."
If it is an issue, is it possible to add parameter for decimal point?

I am afraid separator can also be an issue, since I often read csv's with csv2 function or specify ";" as separator. Is it possible also to specify separator?

Maybe it is possible to set both decimal point and separator globally for a session with one command like in data.pasta package?

It is also possible, that some other issue related to local settings may interfere like a symbol separating rows in matrix (?)

I would like to thank you for sharing this wonderful, very promising project! It ma be great in environments where Excel rules and it is a struggle to promote R as a better tool.

Dear all,

The Excel.Link package rises an error under Windows 10 with Office 2016. The following RCode works correctly under Windows8 and office 2013

library(excel.link)
xl[a1]

However, the same code under Windows 10 (x64) with Office 2016 (x32) leads to the following result:

library(excel.link)
xl[a1]
Error in xls[["Visible"]] : this S4 class is not subsettable

Do you have an idea regarding this error ?

Thanks in advance,
TheChemENG

Hi,

When I open the example R_connection_example.xlsm I get VBA errors, such as "Compile error in hidden module: RServer." I am using R 3.5 and Excel 2013, on Windows8.1.

What are the system requirements?

When opening any Microsoft 97-2003 worksheet that is password protected with xl.read.file(), R aborts with a fatal error. I've updated packags

• excel.link
• XLConnect

There is an error when updating XLConnectJars stating that it is not available in R 4.0 and I wonder if that is the issue.

library(XLConnectJars)
Error: package or namespace load failed for ‘XLConnectJars’:
package ‘XLConnectJars’ was installed before R 4.0.0: please re-install it
install.packages('XLConnectJars')
Installing package into ‘C:/Users//Documents/R/win-library/4.0’
(as ‘lib’ is unspecified)
Warning in install.packages :
package ‘XLConnectJars’ is not available (for R version 4.0.0)

Loading a graph into Excel is slow. It takes up to 3 seconds to load a graph with the following instructions:
png("1.png", width =800, height = 300)
plot(ts)
gp1 = current.graphics(filename = "1.png")
xlrc["'test'!a30"] = gp1
unlink("1.png")

Is there a way to massively improve the loading speed? Generation of the graph directly in R is instant. So it's only the res = objShell.Run("""" & strPath & """ " & Arch & """" & ScriptPath & """", 0, True), that is extremely slow.

Would be amazingly helpful!!!!!

Hi,

I'm using REvaluate and the live R session and it's working very well. I understand the experimental nature of it but I have a costly computation and calling R once is a must have for me.

There is one main issue preventing me from getting it into production - not knowing when the underlying R process throws an error and/or dies. To help get feedback somehow onto the sheet, I wrap all my R calls in a tryCatch and return an 'ok' or error message to communicate what's going on. Something like this:

=REvaluate("xl[['a1']]=errorGuard(someFunction()))

And it works fine. I have a lot of these cells, however, and I tried to swap to a cell reference like so:

=REvaluate("xl[['&cell("address",a1)&"']]=errorGuard(someFunction()))

But this creates an infinite loop. I presume excel sees that a1 changes and assumes that this function cell then needs to be updated - again and again and again.

Is there any way around this, or am I heading down the wrong track?

I'm using R version 4.0.5 on Rstudio connect and get the following install error:
Code:
install.packages("~/PIC Database/packages/excel.link_0.9.11.tar.gz", repos = NULL, type = "source")

Error:
Installing package into ‘/home/p536644/R/x86_64-pc-linux-gnu-library/4.0’
(as ‘lib’ is unspecified)
ERROR: Windows-only package

• removing ‘/home/p536644/R/x86_64-pc-linux-gnu-library/4.0/excel.link’
  Warning in install.packages :
  installation of package ‘/home/p536644/PIC Database/packages/excel.link_0.9.11.tar.gz’ had non-zero exit status

I am running windows - not sure what's causing this.

Hi, when I try to use xl.workbook.save() for password protection, the output file is blank.

This is what I'm doing

# State workbook path
file_workbook_path = "/home/workbook.xlsx" 

#export password protected workbook 
xl.workbook.save(
  file_workbook_path,
  password = "pass",
)

When I execute the function the output xlsx file is empty. All the data has been removed.

Is this a bug?

Have done the install and there is no example xlsm in the doc folder. Also cant find it on the gdemin/excel.link in GitHub

When running the following two lines of code R 4.0.1 aborts. For various versions R 3 there is no problem. I have included run information below:

library(excel.link)
xls <- xl.get.excel()
####################

R version 4.0.1 Patched (2020-06-06 r78651) -- "See Things Now"
Copyright (C) 2020 The R Foundation for Statistical Computing
Platform: x86_64-w64-mingw32/x64 (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

library(excel.link)

To Daniela Khazova who constantly inspires me...

version
_
platform x86_64-w64-mingw32
arch x86_64
os mingw32
system x86_64, mingw32
status Patched
major 4
minor 0.1
year 2020
month 06
day 06
svn rev 78651
language R
version.string R version 4.0.1 Patched (2020-06-06 r78651)
nickname See Things Now
sessionInfo()
R version 4.0.1 Patched (2020-06-06 r78651)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18363)

Matrix products: default

locale:
[1] LC_COLLATE=Danish_Denmark.1252 LC_CTYPE=Danish_Denmark.1252
[3] LC_MONETARY=Danish_Denmark.1252 LC_NUMERIC=C
[5] LC_TIME=Danish_Denmark.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] excel.link_0.9.8-1

loaded via a namespace (and not attached):
[1] compiler_4.0.1 tools_4.0.1

osVersion
[1] "Windows 10 x64 (build 18363)"
xls <- xl.get.excel()

This is where R is completed aborted and shot down.

I have been using excel.link for several years under R3 without any problems, and I would like to continue to use excel.link.

Regards,
Lars

Just wonder if excel.link package allows user to add a password to an unprotected workbook? If not, is it possible to request such feature? Thanks!

Warning message:
package ‘excel.link’ is not available (for R version 3.6.1)

Os: MacOS Mohave

Right now, if I open a workbook, then try to open it again, R hangs while excel asks if I want to re-open & discard changes.

It'd be really nice if there were a wrapper to open a file if and only if it's not open yet. A singleton pattern of sorts.

I know I can wrap xl.workbook.open to call/check xl.workbooks 1st, but it seems it would be common issue.

Thanks!

I am trying to execute an R script using Rscript in VBA Excel book. However, excel.link opens a new instance of excel when the R script is executed and ignores my workbook. How can I point out to my workbook? xl.workbook.activate("my_book") doesn't seem to work. Here is what my R script looks like;

library(excel.link)
 setwd("C:/Yuba_Project")
getwd()
FinalData <- head(iris)
 FinalData
 #xl.workbook.open("Excel_link.xlsm")
 xl.workbook.activate("Excel_link.xlsm")
#xl.sheet.activate("summary")
xlc[a2:h2] = NA
  xlc[a1] = FinalData

And I am trying to execute the above R script from Excel VBA like below:

Sub test()
Dim myPath As String
'ChDir ThisWorkbook.path
myPath = "RScript " & "C:\Users\my_file\Desktop\R_code_5-20-22\Rtest.r"
Call shell(myPath, vbHide)

Question: Does the book has to be close in order to execute the R script?

It would be great if I could control (suppress, close, confirm, etc) excel wanings when opening a file.

For instance, warning 'Update links' prevents me from controlling an excel file and requires my manual interaction in excel.

I imagine adding a new parameter to xl.workbook.open, for instance update_links = FALSE, if possible.

I'm trying out excel.link and it has the functionality I need when I'm directly referring to a cell via the command line or a script, eg xlrc[h4] <- 500 sets cell H4 to 500 (I just switch over to Excel and check). This works, but if I try to set the cell as an object (eg k <- 'H4' ) then refer to that object xlrc[k] <- 500 , I get Error: Exception Returned. I'm trying out excel.link because I need to be able to do what-if scenarios on a spreadsheet with thousands of iterations, so I need to be able to not hand-type in cell references every time! Where did I go wrong?

Hello,

I have a xlsb file that is password protected for writing.
When I call xl.read.file, it shows a popup asking for password, or I can select "Read only" which then works.
However this popu is annoying as I have a lot of tabs to read. Is it possible to pass an argument in xl.read.file to select "read only" by default?

Many thanks

Hi

Is there any way to duplicate worksheet like [holding Crtl key + dragging worksheet tap] ?

It'd be nice to be able to turn off the library load message "To Daniela Khazova who constantly inspires me..."
Or for it to at least respect the "quietly" option:
library(excel.link, quietly=TRUE)

I have a set of monitoring scripts that look at the exit status, and stderr, and this is messing them up.

(yes, I can use suppressMessages(), but that seems a bit extreme)

I appreciate all of the work you've put in. and work on my previous requests!

Thanks!