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
$workbook = $excel.Workbooks.Open($file.name)
-->$workbook = $excel.Workbooks.Open($file.FullName)
. Excel needs the full path and filename. – Theo