How to rename files such that the file extension does not appear within the name using R

Question

My R code currently involves looping through a list of .xlsm files, transforming the data within each sheet through function and then output the files as a set of .xlsx files. Suppose my Filelist consists of only 2 files i.e. Filelist = c("aaa.xlsm", "bbb.xlsm"), my current code, stated below, will output the .xlsx files with file names "aaa.xlsm.xlsx" and "bbb.xlsm.xlsx" respectively.

Filelist <- list.files(pattern = ".xlsm$") #extract list of files from folder that end with xlsm
lapply(Filelist, function(i) {
  df1 <- read_excel(i, sheet = "Trial1")
  df2 <- read_excel(i, sheet = "Trial2")
  df3 <- read_excel(i, sheet = "Trial3")
  df4 <- read_excel(i, sheet = "Trial4")
  write.xlsx(list(df1, df2, df3, df4), file = paste0('Out', i, '.xlsx'),
             sheetName = c("df1", "df2", "df3", "df4"))})

How can I output the files as "aaa.xlsx" and "bbb.xlsx" instead? I understand that I can use the function file.rename after the .xlsx files have been output with the wrong names but I cannot seem to get the right syntax for this.

Filename <- list.files(pattern = ".xlsm.xlsx$")
Newname <- sub("^xlsm.xlsx$", "xlsx", Filename)
file.rename(Filename, Newname)

Answer 1

You could use tools::file_path_sans_ext and add the extension to generate the name directly:

paste0(tools::file_path_sans_ext("aaa.xlm"), ".xlsx")
#> [1] "aaa.xlsx"