2

is there a way to read an open excel file into R?

When an excel file is open in Excel, Excel puts a lock on the file, such as the reading method in R cannot access the file.

Can you circumvent this lock?

Thanks

Edit: this occurs under windows with original excel.

c0bra
  • 1,031
  • 5
  • 22
  • You will need to explain this a bit more. If you open an Excel file with `openxlsx` or `readxl`, the *application Excel* is not using it and is not putting a lock on it. Do you mean how to read a file in R, that is already opened in Excel? – MrGumble Jul 13 '22 at 08:59
  • 1
    @MrGumble edited the question to make it more clear. In short: how to read a file in R, if it is open in Excel. – c0bra Jul 13 '22 at 09:34
  • 1
    I'm having the same issue, I'm sure it used to work with either `readxl` or `openxlsx` but now my xlsx files are staying locked when open – Jonny Phelps Sep 07 '22 at 09:40
  • Is this maybe in issue with OneDrive syncing the Excel file? If I have an Excel file in a Onedrive directory, I cannot read it in using readxl when the file is open, but if I pause syncing onedrive then I can. – andyyy Jan 05 '23 at 09:50

5 Answers5

0

I too do not have problem opening xlsx files that are already open in excel, but if you do i have a workaround that might work:

path_to_xlsx <- "C:/Some/Path/to/test.xlsx"
temp <- tempdir()
file.copy(path_to_xlsx, to = paste0(temp, "/test.xlsx"))
df <- openxlsx::read.xlsx(paste0(temp, "/test.xlsx"))

This copies the file (Which should not be blocked) to a temporary directory, and then loads the file from there. Again, i'm not sure if this is needed, as i do not have the problem you have.

brendbech
  • 399
  • 1
  • 7
0

You could try something like this using the ps package. I've used it on Windows and Mac to read from files that I had downloaded from some web resource and opened in Excel with openxlsx2, but it should work with other packages or programs too.

# get the path to the open file via the ps package
library(ps)
p <- ps()
# get the pid for the current program, in my case Excel on Mac
ppid <- p$pid[grepl("Excel", p$name)]
# get the list of open files for that program
pfiles <- ps_open_files(ps_handle(ppid))
pfile <- pfiles[grepl(".xlsx", pfiles$path),]

# return the path to the file
sel <- grepl("^(.|[^~].*)\\.xlsx", basename(pfile$path))
path <- pfile$path[sel]
Jan Marvin
  • 426
  • 1
  • 4
  • 5
0

The error occurs for .xlsx files opened in Excel and synced with OneDrive/Sharpoint/Teams. The easy solution to read in the file is to close Excel and re-run readxl::read_excel() again.

However, sometimes it would be more convenient to keep it open and still be able to read it into R. The Answer of @brendbech suggests to make a temporary copy. However, it uses file.copy() command which (at least for me) also does not work for open excel files.

The only solution which worked for me (on Windows) was to create a temporary copy of the file using the PowerShell command Copy-Item. See the function below:

read_excel_tmp <- function(path, sheet = NULL, range = NULL, col_names = TRUE,
                           col_types = NULL, na = "", trim_ws = TRUE,
                           skip = 0, n_max = Inf, guess_max = min(1000, n_max),
                           progress = readxl::readxl_progress(),
                           .name_repair = "unique"){
  destfile <- tempfile(fileext = ".xlsx")
  mycmd <- paste0("powershell -command \"Copy-Item '", gsub("/", "\\\\", path),
                  "' -Destination '", destfile, "'\"")
  error_code <- system(mycmd)
  if(error_code != 0) {stop("Powershell's `Copy-Item` was not able to copy-paste the file")}
  readxl::read_excel(path = destfile, sheet = sheet, range = range,
                     col_names = col_names, col_types = col_types, na = na,
                     trim_ws = trim_ws, skip = skip, n_max = n_max,
                     guess_max = guess_max, progress = progress,
                     .name_repair = .name_repair)
}

After you defined the function, the command below should also work for excel files which are currently opened

df <- read_excel_tmp("C:/Some/Path/to/test.xlsx")

(Note: only works on Windows)

retodomax
  • 434
  • 3
  • 14
0

You can read an opened excel file with the R package RDCOMClient :

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlApp[["DisplayAlerts"]] <- FALSE
xlApp[["Visible"]] <- TRUE

path_To_Excel_File <- "D:/excel_File.xlsx"
xlWbk <- xlApp$Workbooks()$Open(path_To_Excel_File, ReadOnly = TRUE)
xlWbk$Sheets(1)$Range("A1")$Value()
Emmanuel Hamel
  • 1,769
  • 7
  • 19
-1

What do you mean by "the reading method in R", and by "cannot access the file" (i.e. what code are you using and what error message do you get exactly)? I'm successfully importing Excel files that are currently open, with something like:

dat <- readxl::read_excel("PATH/TO/FILE.xlsx")

If the file is being edited in Excel, R imports the last saved version.

EDIT: I've now tried it on both Linux and Windows and it still works, at least with version 1.3.1 of 'readxl'.

AMBarbosa
  • 250
  • 1
  • 8