I have the Power....shell

Not sure that’s what He-Man had in mind, but no bother. I do want to talk about the fact that AI is an incredible tool sometimes. Like a second brain if used correctly. Today I needed a script that would find excel files, and then scrub the file for cell contents matching a certain value.

I decided to give Co-Pilot that very prompt, and I’ll be damned if it didn’t deliver with stunning accuracy. It even correctly interpreted my ask to output the matches in a specific format.

I did make a change or two to be fair. But it mostly hit it dead on the nail.

# Define the volume to search (e.g., "D:\")
$volumePath = Read-Host "Enter Folder Path"
# Define the pattern to search for
$pattern = "=NL()-"
# Define the list to store matching file paths
$matchingFiles = @()

# Get all Excel files recursively
$excelFiles = Get-ChildItem -Path $volumePath -Recurse -Include *.xlsx, *.xlsm, *.xls -ErrorAction SilentlyContinue

foreach ($file in $excelFiles) {
    try {
        # Open the Excel file using COM object
        $excel = New-Object -ComObject Excel.Application
        $excel.Visible = $false
        $workbook = $excel.Workbooks.Open($file.FullName, $null, $true)

        foreach ($sheet in $workbook.Sheets) {
            $usedRange = $sheet.UsedRange
            foreach ($cell in $usedRange.Cells) {
                if ($cell.Text -like "*$pattern*") {
                    $matchingFiles += $file.FullName
                    break
                }
            }
        }

        $workbook.Close($false)
        $excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    } catch {
        Write-Warning "Failed to process $($file.FullName): $_"
    }
}

# Output the matching file paths
$matchingFiles | Out-File "MatchingExcelFiles.txt"
Write-Host "Matching files saved to MatchingExcelFiles.txt"