When attempting to overwrite the same excel file multiple times, I would get the following error:
Error in wb$workbook$sheets[[i]]; subscript out of bounds
It would never be an issue when writing to it the first time, but the second time and any other attempt after that would cause this error.
I found the solution and just wanted to post this in case someone else ran into this same problem. The issue was that a few of my tab names contained the characters "->". Removing these characters from the tab name solved the problem.
Code
require(openxlsx)
fn <- 'bug.xlsx' # For bugged version
# fn <- 'fixed.xlsx' # For fixed version
sn <- 'Sheet1'
data <- 'Testing'
wb <- openxlsx::loadWorkbook(fn)
writeData(
wb = wb,
sheet = sn,
x = data,
)
openxlsx::saveWorkbook(wb, fn, overwrite = T)
wb <- openxlsx::loadWorkbook(fn)
writeData(
wb = wb,
sheet = sn,
x = data,
)
openxlsx::saveWorkbook(wb, fn, overwrite = T)
Expected Behavior
Data is written to excel workbook once and then the excel workbook is overwritten.
Actual Behavior
Error in wb$workbook$sheets[[i]]; subscript out of bounds
Problem Summary
When attempting to overwrite the same excel file multiple times, I would get the following error: Error in wb$workbook$sheets[[i]]; subscript out of bounds
It would never be an issue when writing to it the first time, but the second time and any other attempt after that would cause this error.
I found the solution and just wanted to post this in case someone else ran into this same problem. The issue was that a few of my tab names contained the characters "->". Removing these characters from the tab name solved the problem.
Code
Expected Behavior
Data is written to excel workbook once and then the excel workbook is overwritten.
Actual Behavior
Error in wb$workbook$sheets[[i]]; subscript out of bounds
Steps to Reproduce the Problem
bug.xlsx fixed.xlsx
Run code above with the bug.xlsx file. The fixed version is run with the fixed.xlsx file.
sessionInfo()