read.xlsx cannot find Excel file
<p>I want to read a bunch of excel files all located in the same directory and store them in different sheets in a consolidated Excel file.</p>

<p>I initially tried using <code>XLConnect</code> but kept getting the error <code>GC overhead limit exceeded</code>. I stumbled upon <a href="https://stackoverflow.com/questions/21937640/handling-java-lang-outofmemoryerror-when-writing-to-excel-from-r">this question</a> which says that it is a common problem with Java based Excel handling packages such as <code>XLConnect</code> and <code>xlsx</code>. I tried the memory management trick suggested there, but it did not work. One of the comments in one of the comments on the accepted answers suggested using <code>openxls</code> as it based on <code>RCpp</code> and hence avoid this particular problem.</p>

<p>My current code is as follows:</p>

<pre><code>library(openxlsx)
mnth="January"
files &lt;- list.files(path="./Original Files", pattern=mnth, full.names=T, recursive=FALSE)  #pattern match as multiple files are from the same month
# Read them into a list and write to sheet
wb &lt;- createWorkbook()
lapply(files, function(x){
  print(x)
  xlFile&lt;-read.xlsx(xlsxFile = x, sheet = 1, startRow = 2, colNames = T)  #Also tried
  str(xlFile)
  #Create a sheet in the new Excel file called Consolidated.xlsx with the month name
  #Append current data in sheet
})
</code></pre>

<p>The problem I am getting is the error: <code>Error in read.xlsx.default(xlsxFile = x, sheet = 1, startRow = 2, colNames = T) : openxlsx can not read .xls or .xlm files!</code></p>

<p>I have ensured that <code>files</code> variable contains all the files of interest (Ex: January 2015.xls, January 2016.xls, etc). I have also ensured that the path to the file is correct and the Excel files actually exists there.</p>

<p>I have left the writing to Excel as skeleton code as I need to solve the problem with reading the files first.</p>

<p>In case it helps, here is the code attempt with <code>XLConnect</code></p>

<pre><code>library(XLConnect)

setwd("D:/something/something")
mnth="January"
files &lt;- list.files(path="./Original Files", pattern=mnth, full.names=T, recursive=FALSE)
# Read them into a list
df.list = lapply(files, readWorksheetFromFile, sheet=1, startRow=2)
#combine them into a single data frame and write to disk:
df = do.call(rbind, df.list)
rm(df.list)
outputFileName&lt;-"Consolidated.xlsx"
# Load workbook (create if not existing)
wb &lt;- loadWorkbook(outputFileName, create = TRUE)
createSheet(wb, name = mnth)
writeWorksheet(wb,df,sheet = mnth)
#write.xlsx2(df, outputFileName, sheetName = mnth, col.names = T, row.names = F, append = TRUE)
saveWorkbook(wb)

rm(df)
gc()
</code></pre>