Search Multiple Words in Multiple Excel files using Powershell !

Inventory documentation with Excel is something that most infrastructure administrators have to deal with on a daily basis. Sometimes, I have to search for a bulk of IP addresses in multiple Excel files, but I have to do it by opening the Excel files one by one. After investigating how this can be achieved in PowerShell, I found that using comObject is the way to go, as it can be used to automate most Windows applications. Make sure Microsoft Excel is already installed before you run the script.

How to use the script:

You need to create search values.txt file and insert the contents line-by-line that you want to search. (Also the script will automatically create the search values.txt on the first run if not exists). The output files will have the same names as original files appended with _RESULT.xlsx

Then, open powershell console and run with following necessary parameters. You will need to specify -Folder or -File parameter at least.

Example:

.\Excel_search.ps1 C:\MyExcelFiles -Recurse -Color -OpenFile -Grid

Parameters:

-Folder : The folder name in which multiple Microsoft Excel files should already exist (Use it if the -File  is not specified)

-File : The file name of the Microsoft Excel file (Use it if the -Folder is not specified)

-Recurse : Use this with -Folder option to search excel files recursively

-Color : Use this to colorize the values found in the excel files (in darkblue color)

-Grid : Use this to display the summary of search results in grid output windows (See Fig-1) 

-OpenFile : Use this to automatically open the output files when the search operation is completed.

Find Multiple Words in Excel with Powershell
Fig-1: Search Results with Grid View


Find Multiple Words in Excel with Powershell
Fig-2: Results without Grid View
Find Multiple Words in Excel with Powershell
Fig-3: The new 2 files are created in which values are found
Find Multiple Words in Excel with Powershell
Fig-4: Usage and Examples

You can download my script from github.

4 thoughts on “Search Multiple Words in Multiple Excel files using Powershell !”

  1. Hello, I am very new to your blog but I find it very interesting. May I know where can I get the scripts as I could not find any links. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *