Load multiple excel files (with different tabs) and name object after file & tab name

Question

I currently has have 20 Excel files from the same working directory. Each excel file has 5 different tabs. I would like to import the files (in one function) and name each object according to the file name and tab name.

Here is what I have so far. This returns all 20 excel files and turns them all into objects. However, It does it for the FIRST tab only, meaning I only have 20 objects. In this case, I would like 100.

For example: Excel File Name: Apple Tab1: Sheet1 Tab2: Sheet2 Tab3: Sheet3 Tab4: Sheet4 Tab5: Sheet5

So when writing a script, I would want it to output Apple_Sheet1, Apple_Sheet2, etc. for all the excel files within the same working directory. Much appreciated!

    dir_path <- "C:/"
    re_file <- ".xlsx" 

    file.list <- list.files(pattern = "*.xlsx")
    for(i in file.list) {
     assign(sub(".xlsx", "", i), read_excel(i))
    }

Answer 1

dir_path <- "C:/"
re_file <- ".xlsx" 

file.list <- list.files(pattern = "*.xlsx")
for(i in file.list) {
sheets_temp <- excel_sheets(i)
    for(j in sheets_temp) {
       assign(paste0(sub(".xlsx", "", i), "_", j), read_excel(i, j))
                          }
}

Answer 2

I hope this helps

# libraries required -------
require(readxl)
require(writexl)

# Reading and writing files -------------------------
file.list <- list.files(pattern = "*.xlsx")

for (i in file.list) {
  sheets <- excel_sheets(i)
  for (n in sheets) {
    a <- read_excel(path = i, sheet = n)

    # write to the current working directory
    write_xlsx(a, path = sprintf("./%s_%s.xlsx", sub(".xlsx", "", i), n))  #  give a path instead of ./ if you need it in a particular folder. 
                                                                           #  Example: "output/%s_%s.xlsx" make sure the folder exists.

    # or assign it in the global environment
    assign(sprintf("%s_%s", sub(".xlsx", "", i), n), a)
  }

}

Have a great day.