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-2: Results without Grid View
Figure-3: The new 2 files are created in which values are found
Figure-4: Usage and Examples
Awesome work sir, love it!
ReplyDeleteThanks :)
DeleteHello, 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.
ReplyDeleteYou are God mate! Amazing work
ReplyDelete