Rstudio holds Excelfile permission - XLconnect

Question

Since I updated RStudio last day I cannot get Rstudio to drop it's hold on my excelfiles.

I've tried the following to no avail:

  unlink(fileName, recursive = TRUE)
  file.info(fileName)
  file.remove(fileName)

file.info gives the following:

size 2383465
isdir FALSE
mode 666
mtime 2020-08-06 11:02:03
ctime 2020-06-16 13:00:22
atime 2020-08-06 10:40:30
exe no

RStudio is installed by an administrator but I personally don't hold admin-privileges in my organization. file.remove isn't permitted even with unlink (but it's not a dir so...). And I don't really want to remove it, just get Rstudio to drop the hold. The file will be modified in Excel (and saved, which is not permitted since RStudio holds it).

UPDATE:

I guess this is a bug, so all my googling have found no solution so far. One workaround I guess would be able to fix this is if I could programmatically re-initialize the JVM-instance. (for instance, .rs.restartR() do drop the R:s hold on the file)

UPDATE 2 :

The exact same error happens with the openxlsx-package, which is not dependant on Java so the problem is probably with R.

R version 3.6.3 (2020-02-29)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18362)

Matrix products: default

locale:
[1] LC_COLLATE=Swedish_Finland.1252  LC_CTYPE=Swedish_Finland.1252    LC_MONETARY=Swedish_Finland.1252
[4] LC_NUMERIC=C                     LC_TIME=Swedish_Finland.1252 

UPDATE 3:

Still no breakthrough here, I've solved the lock with a bit of hacky-code but it solves the immediate problem atleast:

  .rs.restartR()
  source("~/.Rprofile")

UPDATE 4:

If I do this:

library(XLConnect)

wb <- loadWorkbook("Cars.xlsx", create = TRUE)
createSheet(wb, "Data")
writeWorksheet(wb, cars, "Data")
saveWorkbook(wb)

The first time a open the file, I can save without closing the R-session. If I do that, open the Excel-file and save, and then re-run the above code my R-session hold the permission lock. So it has something within the scope the file is saved.

UPDATE 5:

This is now an open Issue, probably Windows-only bug:

Github - XLconnect

Hyperventilation

Is there a problem while R is not running? If this is the case that would indicate it is not an R related problem. If it is, the problem should be fixed by not saving your environment on closing R and deleting the existing .Rhistory and .Rprofile files. – Pearcy

@Pearcy If I close/restart the R-session the permission hold is dropped, yes. So I only got a problem as long as the R-session that opened and saved the file has some kind of connection to the workbook. This kinda happened after we upgraded both R-studio and R. Reverting to previous version didn't help. I'm not saving the enviroment, so everytime is a clean start. Deleting the .Rprofile sounds like a real pain and almost worse than the disease :p – Hyperventilation

It should not be necessary to delete it continuously. It seems that you at some point during a session had an open connection, and saved the environment at this specific time (or an object that automatically opens a connection). My suggestion is simply to remove the .Rhistory and .Rprofile to clear this variable, and with a bit of care trying to avoid the same situation in the future. :-) – Pearcy

@I tried to but unfortunently it didn't help. I guess it's something with either the Java-objects under the hood or something that's botched with permissions. R might be the cause, but it might be something with excel as well. Since similiar problems in a pure windows/excel-setting when googleing – Hyperventilation

Answer 1

The following function-call inside XLconnect clears the Java-session:

xlcFreeMemory()

gc() in itself didn't work, but xlcFreeMemory also runs the code below, which I guess is what seals the deal:

J("java.lang.Runtime")$getRuntime()$gc()

Hyperventilation