CODEWITHSUNDEEP
excelpowershell
Paul
Paul

Reputation: 551

Powershell - Excel Rename Worksheets base upon filename

I have multiple excel files that I am combining into one excel document with multiple sheets. I would like to rename these sheets based upon the current file being processed name. CODE:

$files = Get-ChildItem "C:\Stuff\" -exclude "Pauls*"
$DestFile = 'C:\Stuff\Pauls Weekly KPI.xlsx' # source's 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 the script has run the final notebook would contain multiple sheets named by their original files name

Upvotes: 0

Views: 5439

Answers (1)

Paul
Paul

Reputation: 551

Solved it by renaming the sheet prior to copying: 

$files = Get-ChildItem "C:\Stuff\" -exclude "Pauls*"
$DestFile = 'C:\Stuff\Pauls Weekly KPI.xlsx' # source's 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

Upvotes: 1

Related Questions