title: How to Combine Excel Workbooks into One Workbook and Name the Worksheets
body: <p>I am trying to combine several Excel workbooks into a single workbook while naming the worksheets the file name of the copied workbook.</p>
  
  <p>I've tried moving $Worksheet.Name within the for loop and outside.  It still only gives a name to the empty worksheet in the new workbook - naming it the last file copied.</p>
  
  <pre><code>$ExcelObject=New-Object -ComObject excel.application
  $ExcelObject.visible=$true
  $ExcelFiles=Get-ChildItem -Path C:\Users\u1\Documents\Testing
  
  $Workbook=$ExcelObject.Workbooks.add()
  $Worksheet=$Workbook.Sheets.Item("Sheet1")
  
  foreach($ExcelFile in $ExcelFiles){
  
  $Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
  $Everysheet=$Everyexcel.sheets.item(1)
  $Everysheet.Copy($Worksheet)
  $Worksheet.Name = [io.path]::GetFileNameWithoutExtension($ExcelFile) 
  $Everyexcel.Close()
  }
  
  $Workbook.SaveAs("C:\Users\u1\Documents\Testing\New\merged.xlsx")
  $ExcelObject.Quit()
  </code></pre>
  
  <p>The new workbook is created (merged.xlsx) but the worksheets are all named Sheet1(...) except for one empty worksheet that is named the last file in the directory.  I also get the following errors.
  Excel cannot access 'New'.  The document may be read-only or encrypted.
  You cannot call a method on a null-valued expression.</p>