Searching Excel In PowerShell
Posted by Daniel Lange on 18 December 2009
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"
$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.