Rename-ExcelTabs-v1

# to run, type this in powershell: &"C:\Users\jostae\Documents\SQL Server Management Studio\FixBOMsplitName.ps1"

# get a list of xlsx files
$allFiles = Get-ChildItem -Path "C:\Users\jostae\Documents\CM release\" -Filter '*.xlsx' -File

# create an Excel object
$xldoc = New-Object -ComObject Excel.Application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false


foreach ($ExcelFile in $AllFiles) {
    # open the file
    $WorkBook = $xldoc.Workbooks.Open($ExcelFile.FullName)
    # count of non AML sheets
    $NotAMLCount = ($WorkBook.Worksheets | where {
        ($_.Name -ne "AML" -and $_.Visible -eq -1)
    }).count
    # non AML sheets that need fixing
    $SplitSheets = $WorkBook.Worksheets | where {
        ($_.Name -ne "AML" -and $_.Name -ne "SPLITS" -and $_.Visible -eq -1)
    }


    # if no sheets are named incorrectly, no action required
    if(($SplitSheets | measure-object).count -eq 0) {
        $WorkBook.Close()
        Write-Verbose -Message "$ExcelFile is correct" -Verbose
    }
    # if there are too many sheets, move to subfolder
    elseif($NotAMLCount -gt 1) {
        $WorkBook.Close()
        Move-Item -Path $ExcelFile.FullName -Destination "C:\Users\jostae\Documents\CM release\tabcheck\"
        Write-Verbose -Message "$ExcelFile has too many sheets" -Verbose
    }

    # otherwise rename non-AML tabs to SPLITS
    elseif($SplitSheets) {
        $SplitSheets.Name = "SPLITS"
        $WorkBook.Save()
        $WorkBook.Close()
        Write-Verbose -Message "$ExcelFile updated" -Verbose
    }
}


# clean up
$xldoc.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xldoc)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()