Lange's Tech Musings

Thoughts, Problems and Solutions

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"

 

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

One Response to “Searching Excel In PowerShell”

  1. David said

    Very slick, this beats the crap out of a line by line search. Well done.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: