title: Copy multiple Excel worksheets from multiple workbooks to a new workbook using PowerShell
body: <p>I have been at this for a while and can't seem to find anything that does exactly what I want.  I was working off this post: <a href="https://stackoverflow.com/questions/23703354/how-to-use-powershell-to-copy-several-excel-worksheets-and-make-a-new-one">How to use PowerShell to copy several Excel worksheets and make a new one</a> but it doesn't do exactly what I am looking for.  I am attempting to copy all worksheets from multiple workbooks and place them in a new workbook.  Ideally, I would like to get the file name from one of the workbooks and use it to name the new file in a new directory.</p>
  
  <p>I have found numerous examples of code that can do this, but there are a couple of key features that I am missing and not sure how to implement them.  Here is an example of what I have working now:</p>
  
  <pre><code>$file1 = 'C:\Users\Desktop\TestFolder\PredictedAttritionReport.xlsx' # source's fullpath
  $file2 = 'C:\Users\Desktop\TestFolder\AdvisorReport' # destination's fullpath
  
  $xl = new-object -c excel.application
  $xl.displayAlerts = $false # don't prompt the user
  $wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
  $wb1 = $xl.workbooks.open($file2) # open target
  $sh1_wb1 = $wb1.sheets.item('Report') # second sheet in destination workbook
  $sheetToCopy = $wb2.sheets.item('Report') # source sheet to copy
  
  $sh1_wb1.Name = "DeleteMe$(get-date -Format hhmmss)" #Extremely unlikely to be a duplicate name
  $sheetToCopy.copy($sh1_wb1)  # copy source sheet to destination workbook
  $sh1_wb1.Delete()
  
  $wb2.close($false) # close source workbook w/o saving
  $wb1.close($true) # close and save destination workbook
  $xl.quit()
  spps -n excel
  </code></pre>
  
  <p>The problem that I have is that I need this to work, such that I don't have to input the actual file name since those names may be different each time they are created and there may be 3 or 4 files with more than one worksheet, where this example works off of only two named files.  Additionally, I would like to be able to copy all worksheets in a file instead of just a single named worksheet, which in this case is 'Report'.  The final piece is saving it as a new Excel file rather than overwriting the existing destination file, but I think I can figure that part out.  </p>