Removes all defined names from XLSX file by manipulating workbook.xml. Only useful in cases where something has gone awry because somebody (totally not you) programmatically created thousands upon thousands of defined names in the file using VBA.

RemoveDefinedNames.ps1

# Main script to accept command-line argument
param (
    [Parameter(Mandatory = $true)]
    [string]$XlsxFilePath
)

# Define the function to remove definedNames node from an XLSX file
function Remove-DefinedNames {
    param (
        [string]$XlsxFilePath
    )

    # Ensure the file exists
    if (-Not (Test-Path $XlsxFilePath)) {
        Write-Error "The file '$XlsxFilePath' does not exist."
        return
    }

    # Check if the file has the .xlsx extension
    if ($XlsxFilePath -notlike "*.xlsx") {
        Write-Error "The file must have an .xlsx extension."
        return
    }

    # Rename .xlsx to .zip for extraction
    $ZipFilePath = "$XlsxFilePath.zip"
    Copy-Item -Path $XlsxFilePath -Destination $ZipFilePath -Force

    # Create a temporary folder to extract the XLSX content
    $TempFolder = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
    try {
        # Create the temporary folder
        New-Item -ItemType Directory -Path $TempFolder | Out-Null

        # Extract the ZIP content into the temporary folder
        Expand-Archive -Path $ZipFilePath -DestinationPath $TempFolder -Force

        # Path to workbook.xml
        $WorkbookXmlPath = Join-Path -Path $TempFolder -ChildPath "xl\workbook.xml"

        if (-Not (Test-Path $WorkbookXmlPath)) {
            Write-Error "The workbook.xml file was not found in the XLSX structure."
            return
        }

        # Load the workbook.xml file
        [xml]$WorkbookXml = Get-Content -Path $WorkbookXmlPath

        # Find and remove the definedNames node
        $DefinedNamesNode = $WorkbookXml.workbook.definedNames
        if ($DefinedNamesNode) {
            $DefinedNamesNode.ParentNode.RemoveChild($DefinedNamesNode) | Out-Null

            # Save the modified workbook.xml file
            $WorkbookXml.Save($WorkbookXmlPath)
        } else {
            Write-Host "No definedNames node found in workbook.xml."
        }

        # Recompress the contents back into a ZIP file
        $TempFilePath = "$XlsxFilePath.temp.zip"
        Compress-Archive -Path (Join-Path -Path $TempFolder -ChildPath '*') -DestinationPath $TempFilePath -Force

        # Replace the original XLSX file with the modified file
        Move-Item -Path $TempFilePath -Destination $XlsxFilePath -Force

        Write-Host "The definedNames node has been removed from '$XlsxFilePath'."
    } catch {
        Write-Error "An error occurred: $_"
    } finally {
        # Clean up the temporary folder and the temporary ZIP file
        if (Test-Path $TempFolder) {
            Remove-Item -Path $TempFolder -Recurse -Force
        }
        if (Test-Path $ZipFilePath) {
            Remove-Item -Path $ZipFilePath -Force
        }
    }
}

Remove-DefinedNames -XlsxFilePath $XlsxFilePath