Searching Excel In PowerShell

Have you ever wanted or needed to search an Excel file for specific values from within a PowerShell script? I know I have. The last time I needed to, I just setup a loop and checked every cell in the column for a match until I encountered a blank cell (or until I exhausted the specified range). This is hardly an elegant solution. I figured there had to be a find method somewhere in Excel that I could use.

With some searching on Google and a bit of reading on MSDN, I’ve figured out how to accomplish it. There is a find method in the Excel NamedRange class. The implementation of this method I use is still relatively simple; I do not pass any of the optional parameters to the find method.

In this example, I open an Excel file, search the first column (column A) for the string “Some Value” and then replace it with the string “Another Value.”

$File = "$pwd\test.xlsx"

# Setup Excel, open $File and set the the first worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(1)

$SearchString = "Some Value"

$Range = $Worksheet.Range("A1").EntireColumn
$Search = $Range.find($SearchString)

$Search.value() = "Another Value"

 

The line:

$Range = $Worksheet.Range("A1").EntireColumn

sets a range for the entire A column. If you prefer, you can specify a fixed range like so:

$Range = $Worksheet.Range("A1","A5")

We then set the output from the find method to the $Search variable in this line:

$Search = $Range.find($SearchString)

We simply pass the search string, in this case $SearchString, as a parameter in the find method on a NamedRange object. Note that the returned value will be $null if the search does not find a match.

And, finally, we change the value of the found cell:

$Search.value() = "Another Value"

There are also FindNext and FindPrevious methods that can be called if you want to find all instances.

Take a look at the following MSDN article for more information on the find method: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.find(VS.80).aspx. For more on automating Excel in PowerShell (or other language), check out: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel(VS.80).aspx. I also found the Developers Guide to the Excel 2007 Application Object to be useful. And, finally, to work with ranges, check out Using the Excel Range Function with PowerShell.

~Daniel

About these ads