#excel #powershell #replace #excel-formula #bulkupdate
Вопрос:
Я использую следующее обновление PowerShell части URL-адреса, используемого в формуле в нескольких файлах Excel в папке.
Но это не работает, если я использую тот же PowerShell для обновления любого другого значения ячейки или части значения, это работает нормально.
$shell = New-Object -ComObject Shell.Application
$folder = $shell.BrowseForFolder(0, 'E:Upwork', 0)
if ([string]::IsNullOrWhiteSpace($folder)) { exit }
# release Shell COM object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($shell)
$oldname = Read-Host 'old value'
if ([string]::IsNullOrWhiteSpace($oldname)) { exit }
$newname = Read-Host 'new value'
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.Value() = $Search.value() -Replace $oldname,$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. Чтобы найти формулы вместо текста ячеек, вам нужно добавить в диапазон дополнительные параметры. Метод Find () . Взгляните на параметр
LookIn
, в котором вы можете задать значение xlFormulas . Когда вы найдете, вам нужно обновить ячейки.Formula
, а не самиValue()
.