Read Currently Open Excel File

[ApexHeel]

Right now openxlsx will only read Excel files that have been saved and closed. It would be great if it could read files that are currently open (writing back to them would also be fantastic, but one step at a time).

Some other packages allow this (excel.link), but are currently broken with R>4.0.

[ycphs]

the package excel link uses a RDCOMClient and you need a valid Microsoft Excel licence. Without dependencies on Java and RDCOM openxlsx has some shortcomings like you mentioned.

Please be aware, that the last relevant code update for RDCOMClient was in 2018.

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.

[JanMarvin]

The ps package offers a way to read the currently open xlsx file. Below attached is a snippet to read the currently open xlsx file (tested on Mac). We could improve this further grep on to lower (on Windows it's uppercase EXCEL) and support other spreadsheet software like (Libreoffice, numbers etc.). Writing back to these files won't be possible on Windows afaik, because open files are locked and cannot be modified. Though the input can be read, modified in R, and saved from R, even replacing the file it is reading from.

I'm not going to add this to the package because of increasing dependencies, but it might be helpful to you @ApexHeel

# 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),]

# read the written file, not the currently open file
sel <- grepl("^(.|[^~].*)\\.xlsx", basename(pfile$path))
path <- pfile$path[sel]

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.