In R I wish to find the latest xlsx file in a folder and then import the data from that file

Question

In R I wish to find the latest xlsx file in a folder and then import the data from that file. All files have the same format. I just keep getting blank. Please advise correct code.

CompanyFileNames <- file.info(list.files 
                              (path = "Y:/...Data", 
                               pattern = "*port.xlsx", 
                               full.names = T))

CompanyFilelatest <- subset(CompanyFileNames, mtime == max(mtime))

CompanyFilelatest <- CompanyFilelatest[0]

Companymonthly <- sapply(CompanyFilelatest, 
              read_excel, simplify=FALSE) 
              %>% bind_rows(.id = "id")                         

write.csv(Companymonthly, "Companymonthly.csv")

Answer 1

What you need is the filepath of the latest file, which is stored as the rowname of CompanyFilelatest. Extract the file path with rownames() and then this should work.

CompanyFileNames <- file.info(list.files 
                              (path = getwd(), 
                                pattern = "*.xlsx", 
                                full.names = T))

CompanyFilelatest <- subset(CompanyFileNames, mtime == max(mtime))

CompanyFilelatest <- rownames(CompanyFilelatest) # use rownames not subseting with 0

Companymonthly <- sapply(CompanyFilelatest, 
                         read_excel, simplify=FALSE) %>% bind_rows(.id = "id")                         

write.csv(Companymonthly, "Companymonthly.csv")