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