title: Combining multiple excel files by the name of their sheets using rstudio
body: <p>In my folder I do have 45 different excel files, each having only one sheet and different data structure. I want to import them in R and then just export them back into one single excel file. Till here I can do with the help of google. However I want the sheet names of the individual sheets in the exported file to be what they were in the original file. Below is what my code looks like till now and I can't seem to figure out how to change it (this code works fine just not the way I want).</p>
  <pre class="lang-r prettyprint-override"><code>setwd(&quot;C:\\Users\\I0510906\\OneDrive - Sanofi\\Desktop\\Insulin\\IQVIA Files\\RAuto\\PERFB\\INSULIN\\&quot;)
  data.files &lt;- list.files(pattern = &quot;*.xlsx&quot;)
  
  # read the files
  files &lt;- lapply(data.files, function(x) read.xlsx2(x, sheetIndex = 1))
  
  
  for (i in 1:length(data.files)){    
    # loading the workbook or excel file
    wbook &lt;- loadWorkbook(data.files[i])   
    
    #extracting sheets from the individual workbooks
    sheet &lt;- getSheets(wbook)                      
    
    for (j in 1:length(sheet)){ 
          assign(paste(&quot;global.&quot;, i,j, sep = &quot;&quot;), 
             read.xlsx2(data.files[i], sheetIndex=j, 
                        as.data.frame=TRUE, header=TRUE))
      
    }
  }
  
  
  for (i in 1:length(data.files)) {
    if(i==1)
      write.xlsx(files[[i]], file=&quot;global-data.xlsx&quot;, 
                 sheetName = paste(&quot;global&quot;,i))
    else 
      write.xlsx(files[[i]], file=&quot;global-data.xlsx&quot;, 
                 sheetName = paste(&quot;global&quot;,i), append=TRUE)
  }
  </code></pre>
  <p><sup>Created on 2022-08-22 by the <a href="https://reprex.tidyverse.org" rel="nofollow noreferrer">reprex package</a> (v2.0.1)</sup></p>
  <p>Currently it is setting the name of sheets in the exported final file to be Global1/2/3 etc. while what I want is the sheet names should be XYZ/ABC/DEF etc. which were exactly at the time of importing them in RStudio.</p>
  <h1>EDIT</h1>
  <p>To make it more clear, I have a folder with 45 files inside. All of them have different structure. My need is not to create a single DF.</p>
  <p>e.g. my first excel file is named &quot;SLIDE1&quot;. The sheet name is also &quot;SHEET1&quot;. Same way the 45th file will have a sheet named &quot;SLIDE45&quot;.</p>
  <p>After importing all the 45 of them, I want to export them back as one single excel file with 45 different worksheets and name of each of the worksheet would be &quot;SLIDE1/SLIDE2/....../SLIDE45&quot; and so on.</p>