CODEWITHSUNDEEP
excelpowershell
eightzero
eightzero

Reputation: 11

Issues with Excel Find/Replace one string in multiple Excel Files

I am trying to write a powershell script that will find and replace one string with another string in all excel files within a directory. When I run the code below I get the errors shown below the code. The first one says that it can't find the file name, but the file is obviously there or it wouldn't know the name. Any ideas?

$object = New-Object -comObject Shell.Application  
$folder = $object.BrowseForFolder(0, 'Select the folder', 0)    
$oldname = Read-Host 'Enter your old string to search for'
$newname = Read-Host 'Enter your new string'

if (!$folder -or !$oldname -or !$newname) {exit} 

$excel = New-Object -comObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $true

foreach ($file in Get-ChildItem -literalPath $folder.self.Path*.xlsx) {
    $workbook = $excel.Workbooks.Open($file.name)    
    foreach ($sheet in $workbook.Sheets) {                    

        foreach ($cell in $sheet) {
            $cell.Address = $cell.Address -replace $oldname, $newname                        
        }            
    }
    $workbook.Save()
    $workbook.Close()
}
$excel.quit()    

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 

Read-Host -Prompt "Press Enter to exit"

Sorry, we couldn't find testDoc.xlsx. Is it possible it was moved, renamed or deleted? At C:\Users\KAbernathy\Documents\replaceTest3.ps1:13 char:5 + $workbook = $excel.Workbooks.Open($file.name) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

You cannot call a method on a null-valued expression. At C:\Users\KAbernathy\Documents\replaceTest3.ps1:20 char:5 + $workbook.Save() + ~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression. At C:\Users\KAbernathy\Documents\replaceTest3.ps1:21 char:5 + $workbook.Close() + ~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull

Exception calling "ReleaseComObject" with "1" argument(s): "Object reference not set to an instance of an object." At C:\Users\KAbernathy\Documents\replaceTest3.ps1:25 char:1 + [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : NullReferenceException

Upvotes: 1

Views: 742

Answers (1)

Theo
Theo

Reputation: 61068

As commented, you are using the $file.Name property where you should use $file.FullName. This is what the error message is about.

Having said that, the code is quite inefficient and could be like below:

$shell = New-Object -ComObject Shell.Application  
$folder = $shell.BrowseForFolder(0, 'Select the folder', 0)    
if ([string]::IsNullOrWhiteSpace($folder)) { exit }

# release Shell COM object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($shell)

$oldname = Read-Host 'Enter your old string to search for'
if ([string]::IsNullOrWhiteSpace($oldname)) { exit }

$newname = Read-Host 'Enter your new string'
if ([string]::IsNullOrWhiteSpace($newname)) { exit }

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $true

Get-ChildItem -Path $folder.self.Path -Filter '*.xlsx' -File | ForEach-Object {
    $workbook = $excel.Workbooks.Open($_.FullName)    
    foreach ($sheet in $workbook.Sheets) {
        $range = $sheet.UsedRange              
        $search = $range.Find($oldname)
        if ($search) {
            $firstFind = $search.Address
            do {
                $search.Value = $newname
                $search = $range.FindNext($search)
            } while ( $null -ne $search -and $search.Address -ne $firstFind)

        }
    }
    $workbook.Save()
    $workbook.Close()
}
$excel.Quit()    

# clean-up used Excel COM objects
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 

Read-Host -Prompt "Press Enter to exit"
  1. By setting the Visible property to $false, Excel won't have to update the screen on every replacement, saving a lot of time.
  2. Using the UsedRange property of the sheet combined with Excels own Find and FindNext methods would be more efficient than looping over every cell in the sheet yourself.

Upvotes: 1

Related Questions