Author Posts

September 10, 2014 at 1:17 pm

I am trying to find a value in a merge cell. So can then start processing the cells below it. Setting a Range and then using the Find method could not find the value.

thanks
Jon

September 11, 2014 at 5:47 am

Sorry – we don't have a ton of people here who do Excel programming. In general, most of us try to avoid it like the plague because it's so complicated and time-consuming. Give me a SQL Server database any day!

September 11, 2014 at 7:17 am

The excel spread is created by HR. I figure it out though. This is this a snippet. Also Get-filename is a function that open a windows so I can select the file. $excelsearchRange is how I was able to search for a value of a merge cell.
pre

$objExcel= new-object -comobject excel.application

$objExcel.Visible=$false

$filePath= Get-FileName "C:\Reports"

write-host "Processing File $filepath"

$arrayLHuser= New-Object System.Collections.ArrayList

Write-Host "Opening Workook"

$workbook= $objExcel.Workbooks.Open($filePath)

$spreadsheet = $workbook.worksheets.item(1)

$excelSearchString = "NAMES TO BE ADDED TO NOTIFICATION PROGRAM"

(b)$excelSearchRange = $spreadsheet.range("A1").mergeArea(/b)

$search = $excelSearchRange.find($excelSearchString)

$intRow=$search.row+2
/pre