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 = $$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: For more on automating Excel in PowerShell (or other language), check out: 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.


4 Responses to “Searching Excel In PowerShell”

  1. David said

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

  2. Håkan said

    Hi! Copied you script and created a test file. The thing that dosent work for me is the set new value part. I get the following error.
    “You cannot call a method on a null-valued expression.” Seems like the valuse $search is empty.

    Any ideas what can be wrong?


  3. Håkan said

    to add some more information is that the value I search for is not in the A column its in the B, but shouldent I still get referens value in $search

Leave a Reply

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

You are commenting using your 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


Get every new post delivered to your Inbox.

%d bloggers like this: