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

Question

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.

There is no need to load the package RDCOMClient explicitly, it will be automatically loaded as soon it's needed the first time. — Andri Signorell, Jul 04 '18 at 22:28

score 0 · Answer 1 · answered Jul 04 '18 at 07:44

You probably forgot to call the library function or select the range in the excel sheet

Select the range A1:B21 in the sheet1 of your excel file and run the below code:

install.packages("DescTools")
install.packages("RDCOMClient")

library(DescTools)
library(RDCOMClient)

BMI <- XLGetRange(header=TRUE)

score 0 · Answer 2 · answered Jul 07 '18 at 07:03

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.

score 0 · Answer 3 · answered Jan 28 '20 at 13:04

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.

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

3 Answers3