CODEWITHSUNDEEP
c#powershell
bishch
bishch

Reputation: 25

Powershell to rename particular excel sheet

I need to quickly rename particular excel sheet. The xlsx file itself has many of them (dates - I have to point out the newest by name change). The only thing I found is the ability to change the name of the first worksheet. Any hints guys? I'm a total layman when it comes to c#

$xlspath = "D:\New folder\Testing.xlsx"
$xldoc = new-object -comobject Excel.application
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "Result"
$worksheet.SaveAS = ($xlspath)
$worksheet.Close()
$xldoc.Quit()

Upvotes: 1

Views: 366

Answers (1)

Theo
Theo

Reputation: 61068

Without knowing the date format you have used to name the worksheets, below code should do what you want:

$xlspath  = "D:\Test\Testing.xlsx"
$xldoc    = New-Object -ComObject Excel.Application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false

$workbook = $xldoc.Workbooks.Open($xlspath )
# find the worksheet that is named for the latest date
$latestSheet = ($workbook.WorkSheets | 
                Sort-Object @{Expression = { (Get-Date $_.Name) }} | 
                Select-Object -Last 1).Name
# get the worksheet object by its name
$worksheet = $workbook.WorkSheets.Item($latestSheet)
# and rename it
$worksheet.Name = "Result"
# close and save
$workbook.Close($true)  # $true means 'save the changes'
$xldoc.Quit()

# Important! release the COM objects from memory
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xldoc)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Before:

enter image description here

After:

enter image description here

As you can see my date format is Dutch (NL), so in the form of dd-MM-yyyy. Yours could be different, so you may need to change Get-Date $_.Name into [datetime]$_.Name

Edit

If you have more excel files like that in a folder, you can do this:

$xlspath  = "D:\Test"
$xldoc    = New-Object -ComObject Excel.Application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false

# get the files and iterate over them
Get-ChildItem -Path $xlspath -Filter '*.xlsx' -File | ForEach-Object {
    $workbook = $xldoc.Workbooks.Open($_.FullName )
    # test if there isn't already a worksheet named 'Result' in that file
    try {
        $worksheet = $workbook.WorkSheets.Item("Result")
        Write-Warning "File '$($_.FullName)' already has a sheet called 'Result'. Skipping file."
        $workbook.Close($false)
        continue  # skip this file and proceed with the next
    }
    catch {}

    # find the worksheet that is named for the latest date
    $latestSheet = ($workbook.WorkSheets | 
                    Sort-Object @{Expression = { (Get-Date $_.Name) }} | 
                    Select-Object -Last 1).Name
    # get the worksheet object by its name
    $worksheet = $workbook.WorkSheets.Item($latestSheet)
    # and rename it
    $worksheet.Name = "Result"
    # close and save
    $workbook.Close($true)  # $true means 'save the changes'
}
$xldoc.Quit()

# Important! release the COM objects from memory
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xldoc)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Upvotes: 1

Related Questions