Powershell - Excel Rename Database by File Name

Question

I have several excel files that I merge into a single excel document with multiple sheets. I would like to rename these sheets based on the current file name being processed. THE CODE:

$files = Get-ChildItem "C:\Stuff\" -exclude "Pauls*"
$DestFile = 'C:\Stuff\Pauls Weekly KPI.xlsx' # source fullpath
foreach($file in $files)
{
  $baseName = [System.IO.Path]::GetFileNameWithoutExtension($file.fullname)
  Write-Host $baseName
  $xl = new-object -c excel.application
  $xl.displayAlerts = $false # don't prompt the user
  $wb1 = $xl.workbooks.open($file, $null, $true) # open source, readonly
  $wb2 = $xl.workbooks.open($DestFile) # open target
  $sh1_wb2 = $wb2.sheets.item(1) # first sheet in destination workbook

  $sheetToCopy = $wb1.sheets.item('Report') # source sheet to copy

  $sheetToCopy.copy($sh1_wb2) # copy source sheet to destination workbook

  $wb1.close($false) # close source workbook w/o saving
  $wb2.close($true) # close and save destination workbook
}
$xl.quit()
spps -n excel

Example: After running the script, the destination laptop will contain several sheets named after their source files

Answer 1

Solved by renaming the sheet before copying:

$files = Get-ChildItem "C:\Stuff\" -exclude "Pauls*"
$DestFile = 'C:\Stuff\Pauls Weekly KPI.xlsx' # source fullpath

$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user

foreach($file in $files){

$baseName = [System.IO.Path]::GetFileNameWithoutExtension($file.fullname)
Write-Host $baseName

$wb1 = $xl.workbooks.open($file, $null, $true) # open source, readonly
$wb2 = $xl.workbooks.open($DestFile) # open target

$sh1_wb2 = $wb2.sheets.item(1) # first sheet in destination workbook


$sheetToCopy = $wb1.sheets.item('Report') # source sheet to copy
$sheetToCopy.name = $baseName

$sheetToCopy.copy($sh1_wb2) # copy source sheet to destination workbook

$wb1.close($false) # close source workbook w/o saving
$wb2.close($true) # close and save destination workbook


}

$xl.quit()
spps -n excel