saveWorkbook() does not drop an error when overwriting an open existing file

[pakom]

I am not sure that this is bug or intended behavior. I did not use the openxlsx package for a while and few days ago I needed it for a small task (I have updated to v4.2.3). Consider this minimal example:

library(openxlsx)

x <- data.frame(a = 1:10, b = 11:20)

my.wb <- createWorkbook()

addWorksheet(wb = my.wb, sheetName = "Data")

writeData(wb = my.wb, sheet = "Data", x = x)

worksheetOrder(wb = my.wb)

saveWorkbook(wb = my.wb, file = "C:/temp/text.xlsx", overwrite = TRUE)

It all works fine, the MS Excel file is overwritten, as required. However, I remember that when using the package several months ago, the function dropped an error if the MS Excel file exists and it's open. With the latest version, if the file is open in MS Excel, no error is thrown and the file is (of course) not overwritten. Is this desired behavior?

I tried to add a check if the file is open using the proposed solutions here. without any success. Using the first solution corrupted the file, using the second did nothing.

Any recommendations on how to get the error back?

I am using Windows 10 and don't know what is the behavior in other operating systems.

Thank you in advance,
pakom

[cwolcott]

Interesting, I also have the latest code v4.2.3, but am running on MacOS using Microsoft Excel for Mac Version 16.48 and everything works just fine.

I typical have my excel file open, then rerun the R script which overwrites the file without any issues even though it is open.

[pakom]

Thank you cwolcott.

Do I understand correctly that the file is overwritten while being open, without warning/error messages and, although being open, the file is updated?

[cwolcott]

@pakom correct. The file is overwritten even though it is open, without any warning/error message and although being opened the file is updated.

I just added returnValue = TRUE
abc <- saveWorkbook(wb = my.wb, file = "C:/temp/text.xlsx", overwrite=TRUE, returnValue=TRUE)
print(abc)

TRUE was returned, meaning success. Documentation - saveWorkbook

Is there any additional log files I should look at for more details?

[pakom]

@cwolcott That's interesting, on Windows it behaves differently. I added returnValue=TRUE to saveWorkbook. When the file is closed, TRUE is returned to the console and the file is overwritten. When the file is opened, the following message is returned:

`<simpleWarning in file.create(to[okay]): cannot create file 'C:/temp/text.xlsx', reason 'Permission denied'>`

I suppose that this is because of the operating system. UNIX-like systems allow overwriting opened files. I don't have Linux installed on this machine, but I suppose it will behave the same as on MacOS.

[cwolcott]

@pakom, So now you can determine if the process of writing the xlsx file was successful or not with the returnValue=TRUE. I agree that the OS is allowing/disallowing the overwriting of open files.

On macOS I locked the file and am receiving the following warning:

<simpleWarning in file.create(to[okay]): cannot create file '/Users/wolcottce/Documents/Projects/MTP/R/atestresults/RBD_CCR_001_010521.wellMeanTemp.xlsx', reason 'Operation not permitted'>

So in your case if the process fails what do you want to do? Trying saving it again, but with a timestamp so it is unique or stop processing and log the issue?

General info - Overwriting an Open File in Windows

[pakom]

@cwolcott Thank you for following this.

I just tested it on Linux, it does completely different thing compared to Windows and MacOS when adding returnValue = TRUE. Whether the file is opened or not, saveWorkbook always returns TRUE. When the file is opened, thou, it is never overwritten.

Why I'm asking all this is because I have a function which drops a custom error message like this:

withCallingHandlers(
    saveWorkbook(wb = my.wb, file = destination.file, overwrite = TRUE),
    warning = function(w){
      if(grepl("reason 'Permission denied'", w$message)){
        stop('The file in "output.file" (', destination.file, ') exists and is open, it cannot be overwritten. Please close the file and try again.', call. = FALSE)
      } else {
        message(w$message)
      }
    })

It was catching the original warning message thrown from the saveWorkbook and then producing more informative error message. It did not matter if the OS is Windows or Linux, it always worked (don't know about MacOS, I never had computer with it). I can modify the code from above to work in Windows, but I'm not sure about Linux.

[cwolcott]

Wow, very interesting to know. So I am new in R, and you have passed my level of knowledge.

Is the basic question why is the saveWorkbook no longer throwing an error that can be caught?

Good luck! I will be watching the thread to learn more.

[pakom]

@cwolcott Thank you, although I don't perceive myself so knowledgeable in R.

The basic question now evolved into "Why saveWorkbook behaves differently on different OSes and why no error is thrown by default when the file is open (regardless whether we want to catch it or not)?"

[JMPivette]

I just looked at the code and indeed there has been a change in the following line in version 4.2.2:

openxlsx/R/wrappers.R

Line 122 in fade982

result<-tryCatch(file.copy(from = xlsx_file, to = file, overwrite = overwrite),

This changed happened to fix this issue : #71

To answer your questions:

Why saveWorkbook behaves differently on different OSes ?

• saveWorkbook relies on base R file.copy() and the locking mechanism seems to be different on different OS.

why no error is thrown by default when the file is open?

• That's a good question. Before 4.2.2, any warning from file.copy() would send a warning when using saveWorkbook()
  Since 4.2.2 warnings are not visible but are stored in result (using returnValue argument).

One way to solve is to keep saving the warning message(4.2.2 behavior) and at the same time to send the warnings to the standard output (before 4.2.2 behavior).

@ycphs , what's your opinion on this?

[cwolcott]

I really like the explanation in issue #71. Thank you for the teaching moment.

I will add the following to my code for the time being, since I haven't thought through if I need to handle this in more detail.

  result <- saveWorkbook(wb, xlsxMeanTempFile, overwrite=TRUE, returnValue=TRUE)
  if (!isTRUE(result)) print(paste0('** ', result))

[github-actions]

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.