R Openxlsx package (version 4.2.2) - Error in file(description = xlsxFile) : invalid 'description' argument

Question

Problem: I am experiencing some very odd behaviour with openxlsx read.xlsx.

Set up. I have a directory of data (called "Data"!!!) on my Onedrive containing a whole bunch of Excel spreadsheets. The spreadsheets are similar in structure but not identical and I'm using R (3.6.1) to examine and merge them. I am using R studio with the working directory set to the same directory as the data.

The sheet of interest in each workbook is called ProcData containing a table called DataTable and is built using Windows power query unpivoting data from another sheet in the workbook.

Environmental declarations

library(Openxlsx)
filelist <- list("nAKRapidTransit2019.xlsx","nAKroadpricing2016.xlsx",...+18 more )
targetsheet <- "ProcData"

OK, now this works. It reads the table just fine:

read.xlsx("nAKRapidTransit2019.xlsx", sheet= "ProcData")

However what I want to do is read all 20 sheets into a list of data tables. I have code to read the filelist sequence into a list. That isn't the problem. The problem is that for some weird reason the read.xlsx function is baulking when I give it objects to work with. So

j <- 6
filelist[j]
targetsheet
read.xlsx(filelist[j], sheet=targetsheet)

Generates:

[[1]]
[1] "tAKBusCyclelane2019.xlsx"

[1] "ProcData"

Error in file(description = xlsxFile) : invalid 'description' argument

And no, the files are not open in Excel. I can set j to any number between 1 and 20 and the error message is the same.

Any explanations of what the error message is meant to mean and what I do about it would be gratefully received.

Answer 1

Solved!

the

filelist[j] 

bit is wrong.

it should be

filelist[[j]]

of course!