Read highlighted excel cells via PowerShell

Question

My worksheet looks like this: (https://i.sstatic.net/oJyJ6PA4.png) I want to automatically determine which cell is highlighted and obtain its value using PowerShell. It will be"3" in this case (from the F5 cell).

Here's my effort:

#Define the path to the Excel file $excelFilePath = "C:\Users\Documents\Book3.xlsx"

$objExcel=New-Object -ComObject Excel.Application $objExcel.Visible=$True $workbook=$objExcel.Workbooks.Open($excelFilePath) $sheet = $workbook.Worksheets.Item('Sheet1') $sheet.activate()

#Read data from a specific cell based on row number and column number and store it in a variable called $value:
#$value = $Worksheet.Cells.Item($rowNumber,$columnNumber).text

#Initialize an array to hold values of highlighted cells
    $highlightedValues = @()

#Define the range of rows and columns to check 
    $startRow = 1
    $endRow = 10
    $columns = 'A','B','C','D','E','F'  # Columns A to F
    for ($rowNumber = $startRow; $rowNumber -le $endRow; $rowNumber++) { Get the cell using row number and column letter
            $cell = $worksheet.Cells.Item($rowNumber, $columnLetter)

#Check if the cell's interior color is not automatic (not highlighted) 
    if ($cell.Interior.ColorIndex -ne -4142) {  # -4142 represents xlNone Add the cell value to the array
                $highlightedValues += $cell.Value()
            }
        } Print highlighted cell values to the PowerShell console if ($highlightedValues.Count -gt 0) { Write-Host "Highlighted cell values:"
        $highlightedValues | ForEach-Object { Write-Host $_ } else { Write-Host "No highlighted cells found."
    }


#Save the excel workbook: $Workbook.Save()

#Quit from excel application:

Is there a more effective method? Appreciate any advice.

Answer 1

The most efficient way is to don't iterate over cells and allow Excel to calculate the result itself.

One of the solutions can be to add the Name into the workbook

GetMyInfo=TOROW(MAP(Sheet1!A1:F10,LAMBDA(itm,IF(GET.CELL(63,itm)=0,1/0,itm))),2)

This calculation provides the array of values from cell with the non-automatic background color of the range referenced in the formula. You can modify the formula to add the range as the parameter.

This name can be permanently added into target workbooks, or you can add it dynamically in the script (due to some restrictions we can't avoid using Name to perform this calculation).

Then evaluate it in the ps script

$values = $objExcel.Evaluate("=GetMyInfo")

It should work with $objExcel, otherwise use $sheet.

Ask me for further help to modify the solution.