Replaces specified numbers in an Excel worksheet, excluding certain columns.

    The Set-CT365SPDistinctNumber function opens an Excel file and replaces occurrences of a specified number in a given worksheet.
    It excludes specific columns ("Template" and "TimeZone") from this operation.
    The primary reason for this is to be able to create new Sharepoint Team sites while the others are deleting.

    The path to the Excel file that contains the worksheet to be modified.

.PARAMETER WorksheetName
    The name of the worksheet within the Excel file where the replacements will be made.

    The number to find in the worksheet. This number will be replaced wherever it is found, except in the excluded columns.

.PARAMETER ReplaceNumber
    The number that will replace the FindNumber in the worksheet.

    Set-CT365SPDistinctNumber -FilePath "C:\Documents\example.xlsx" -WorksheetName "Sheet1" -FindNumber "36" -ReplaceNumber "37"

    This command replaces all occurrences of the number 36 with 37 in the worksheet named "Sheet1" of the Excel file located at "C:\Documents\example.xlsx", excluding the "Template" and "TimeZone" columns.

    None. You cannot pipe objects to Set-CT365SPDistinctNumber.

    None. This function does not generate any output.

    This function requires the ImportExcel module to be installed.

.LINK - The ImportExcel PowerShell module


function Set-CT365SPDistinctNumber {
    param (




    # Import the ImportExcel module
    Import-Module ImportExcel

    # Open the Excel package
    $excelPackage = Open-ExcelPackage -Path $FilePath

    try {
        $worksheet = $excelPackage.Workbook.Worksheets[$WorksheetName]
        if ($null -eq $worksheet) {
            throw "Worksheet '$WorksheetName' not found."

        # Get the indices of the columns to exclude
        $excludedColumns = @("Template", "TimeZone").ForEach({
            $worksheet.Dimension.Start.Column..$worksheet.Dimension.End.Column |
            Where-Object { $worksheet.Cells[1, $_].Text -eq $_ } | 
            ForEach-Object { [OfficeOpenXml.ExcelCellAddress]::GetColumnLetter($_) }

        # Initialize a counter for replacements
        $replacementCount = 0

        # Find and replace the numbers, skipping the excluded columns
            $_.Value -like "*$FindNumber*" -and 
            -not ($excludedColumns -contains [OfficeOpenXml.ExcelCellAddress]::GetColumnLetter($_.Start.Column))
            if ($_ -ne $null -and $null -ne $_.Value -and $_.Value -like "*$FindNumber*") {
                $_.Value = $_.Value -replace $FindNumber, $ReplaceNumber

        # Check if the number of replacements is as expected
        if ($replacementCount -eq 0) {
            throw "No replacements were made for the number '$FindNumber'."
        } elseif ($replacementCount -ne 12) {
            Write-PSFMessage -Message "Unexpected number of replacements: $replacementCount. Expected 12." -Level Error
        } else {
            Write-PSFMessage -Message "Exactly 12 replacements were made for the number '$FindNumber'." -Level Host

        # Save and close the Excel package
        Close-ExcelPackage $excelPackage
    catch {