Hello everyone!
I have Script bellow, and it’s works for string, but I need search the function into the excel cell, not string. It´s possible?
For exemple: $keyword = “SUM” or $keyword = “=CONCAT”
Whem the Script finds key ($keyword ), the file (excel) will move to other directory ($destination)
Get-Process -Name EXCEL | Stop-Process -ErrorAction SilentlyContinue
$source = 'C:\'
$destination = 'C:\WithFunction'
$keyword = "SUM"
$files = Get-ChildItem -Path $source | Where-Object {$_.Name -like '*.xls*' -and '*.xlsx*'}
foreach ($file in $files)
{
Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)
$path = $file.FullName
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$workBook = $objExcel.Workbooks.Open($path)
$sheets = $workBook.Sheets
$results = @()
foreach ($sheet in $sheets)
{
$sheetName = $sheet.Name
$filter1 = $workBook.Sheets.Item("$sheetName").UsedRange.Find("$keyword")
if ($filter1.Text -eq $keyword -or $filter1.Text -match '^OT[- ].*' )
{
$results = $true
}
$close = $workBook.Close()
}
if ($results -eq $true)
{
Move-Item -Path $path -Destination $destination -Verbose
}
If ($results -ne $true)
{
Write-Host $path do not contain $keyword1 -ForegroundColor Yellow
}
}
Tks!