Read open excel file in R

Question

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.

Filmer

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? – Genisia

@Genisia edited the question to make it more clear. In short: how to read a file in R, if it is open in Excel. – Filmer

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 – Sheryl

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. – Gratitude

Answer 1

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)

Vocoid

Answer 2

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.

Wadleigh

Nice approach, but doesn't work either. A file copy is created, but has 0 bytes. – Filmer

Interesting. Maybe the file opened with excel was never saved? – Wadleigh

Or maybe the file is encrypted or password-protected? – Lely

Answer 3

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]

Temporal

Answer 4

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()

Selfsatisfied

Answer 5

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'.

Lely

This does not work. read_excel("file.xlsx") Error in utils::unzip(zip_path, list = TRUE) : zip file 'file.xlsx' cannot be opened – Filmer

Just added an edit to my answer above. Have you tried updating the 'readxl' package? – Lely

Version 1.4.0 of readxl throws the same error. – Filmer