Search Multiple Words in Multiple Excel files using Powershell !

Inventory documentation with excel a thing that most Infra admins has to deal with on a daily basis. Sometimes, I have to look for bulk of IP Addresses on multiple excel files but I have do it by opening the excel files one-by-one. So, after checking how it can be achieved in powershell, I found comObject is the way to go, which 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.

               Figure-1: Search Results with Grid View

                        Figure-2: Results without Grid View

                        Figure-3: The new 2 files are created in which values are found

                              Figure-4: Usage and Examples