Submitted By: Kent Finkle

Replaces a specified text string within an Excel spreadsheet.

PowerShell
Edit|Remove
$comments = @'
Script name: Replace-ExcelText.ps1 
Created on: Thursday, August 16, 2007 
Author: Kent Finkle 
Purpose: How can I use Windows Powershell to 
Replace Text in an Excel Spreadsheet? 
'@ 
#----------------------------------------------------- 
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
} 
#----------------------------------------------------- 
 
$xl = new-object -comobject excel.application 
$xl.Visible = $True 
 
$wb = $xl.Workbooks.Add() 

$ws = $wb.Worksheets.Item(1) 
 
# put the text there 
$r = $ws.Range("a10") 

$r.Value() = "C:\Test\Image.jpg" 
 
$r = $ws.UsedRange 
 
# replace it 
$a = $r.Replace("C:\Test\Image.jpg", "C:\Backup\Image.jpg") 
 
$a = Release-Ref($r) 
$a = Release-Ref($ws) 
$a = Release-Ref($wb) 
$a = Release-Ref($xl)