CODEWITHSUNDEEP
rexcelrdcomclientdesctools
Herman Tan
Herman Tan

Reputation: 49

DescTools::XLGetRange() fails with sel$Cells() Error: attempt to apply non-function

I am running R version 3.5.0

I have installed the packages:

install.packages("DescTools")
install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")

I have run Excel and selected a range of values.

Then I run this code in RStudio:

library(DescTools)
BMI <- XLGetRange(header=TRUE)

I get this error message:

Loading required namespace: RDCOMClient
Error in sel$Cells() : attempt to apply non-function

When I run this code:

BMI <- XLGetRange(file="C:/Users/path/Excel to R.xlsx", sheet = "Sheet1", range="A1:B21")

It works fine.

There is an error in importing data from Excel where you select a range in a running Excel Application.

Help would be appreciated in solving this problem.

Upvotes: 3

Views: 417

Answers (2)

Andri Signorell
Andri Signorell

Reputation: 1309

This error occurs, when an excel instance remains invisibly in the background. The function GetNewXL tries to get a handle to a new instance and somehow does not get it correctly. If you see this error, close all running Excel tasks and run DescTools::XLKill(), which will end all the Excel instances running invisibly.

Upvotes: 0

Herman Tan
Herman Tan

Reputation: 49

I checked the source code of XLGetRange()

......
if(is.null(file)){
    xl <- GetCurrXL()
    ws <- xl$ActiveSheet()
    if(is.null(range)) {
      # if there is a selection in XL then use it, if only one cell selected use currentregion
      sel <- xl$Selection()
      if(sel$Cells()$Count() == 1 ){
        range <- xl$ActiveCell()$CurrentRegion()$Address(FALSE, FALSE)
      }
.....

xl <- GetCurrXL() returns

An object of class "COMIDispatch"
Slot "ref":
<pointer: 0x000000000011b688>

xl$ActiveSheet() returns NULL xl$Selection() returns NULL

When I terminated the Excel application.

xl <- GetCurrXL() still returns

An object of class "COMIDispatch"
Slot "ref":
<pointer: 0x000000000011b688>

I decided to restart my laptop and start RStudio.

Now it is working like normal

library(DescTools)
BMI <- XLGetRange(header=TRUE)

BMI returns the selected range of cells in Excel.

Upvotes: -1

Related Questions