powershell and Excel

Dear all
I’m working on a script to remove email addresses from an excel file
the script as such works fine when I search for just 1 email address save and close. however when I work with multiple email addresses then I get a popup that this workbook is used by another process and I’m not able to edit the file to remove the other email addresss
[pre]

$File = “C:\temp\GlobalDDI.xlsx”
$users = import-csv C:\temp\toRemove.csv

foreach($user in $users){
$SearchString = $user.email
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($File)
for($i = 1; $i -lt $($Workbook.Sheets.Count() + 1); $i++){
$Range = $Workbook.Sheets.Item($i).Range(“A:Z”)
$Target = $Range.Find($SearchString)
if($target -ne $null){
$First = $Target
Do
{
$target.value() =""
Write-Host “$i $($Target.AddressLocal())”
$Target = $Range.FindNext($Target)
}
While ($Target -ne $NULL -and $Target.AddressLocal() -ne $First.AddressLocal())
}
}
$workbook.save()
}

$excel.quit
[/pre]

is there a way that I can run this without using multiple com objects?
or do I need to close the com objects each and every time?

 

Paul

you are creating a new excel object inside the foreach loop, don’t do that. for each iteration of the loop, you try to create an object that already exists.

create the object before the loop so that only one object will be created and work with that object.