0

Using the name= parameter in the writeData function in the openxlsx package creates a named range in Excel. This name does not seem usable because there is no worksheet associated with the name. Here is the example R code that creates the spreadsheet:

library(openxlsx)
wb<-createWorkbook()
wks<-"Example"
addWorksheet(wb, wks)
writeData(wb,wks,x="A Cell",
      startCol = 1, startRow = 1, name="ExampleRangeName")
saveWorkbook(wb,file="example.xlsx",overwrite = TRUE)

The example.xlsx workbook contains a named range that refers to "=NA!$A$1:$A$1". It should refer to "=Example!$A$1:$A$1". Any suggestions how to make this work? Thanks.

rmacey
  • 599
  • 3
  • 22
  • You have found [a bug](https://github.com/awalker89/openxlsx/issues). The problem is when `writeData` tries to assign the named range with `wb$sheet_names[sheet]` (which produces an `NA`), because `wb$sheet_names` is an unnamed `character` vector. If you try it with `sheet=1` it works as expected. The docs state that `sheet=` *"Can be the worksheet index or name"*, so this is either a problem with the documentation or (more likely) with the function behavior itself. – r2evans Sep 11 '18 at 14:03
  • I have tried this in Excel 2016 and it works as expected. The formulas name manager lists the named range as `=Example!$A$1:$A$1` and the worksheet name is `Example` – R. Schifini Sep 11 '18 at 14:09
  • 1
    (I should note that I currently have `openxlsx-4.0.17` installed, whereas CRAN is reporting 4.1.0 as current. It's possible that *I* have a version issue, too :-) When I tested on R-3.5.1 and `openxlsx-4.1.0`, this problem **went away**, suggesting you do not need to file a bug report. Good news! – r2evans Sep 11 '18 at 15:00

0 Answers0