I was tasked with searching for data within Word and Excel files similar to something I'd written a while back but an expansion of that original request.  Instead of searching for a specific term within the filename, we are now searching inside of the files looking for a specific phrase.  When I was finished, I gained some additional knowledge -- some good and some not so good.  I started out with a myopic mindset but realized the gravity of the situation once I moved from my test environment to the live system.

That's not to say that it doesn't work so let's walk through the test situation and then I can elaborate on the issues.

We start off with our test folder which contains a dozen or so Excel files.  Within a couple of those Excel files, I've inserted a username and password.  In one of the folders, I've created a subfolder to ensure the -Recurse function was working.  

Our test folder view:





Performing a quick search to see the entire list of files:

Our script:


$SearchText = 'password'
$Username=$env:UserName
$Date=(Get-Date -Format o).Replace(":","-")
$Excel = New-Object -ComObject Excel.Application
$Files = Get-ChildItem c:\users\$Username\documents\test_folder\*.xlsx -Recurse
ForEach($File in $Files){
try {
$Workbook = $Excel.Workbooks.Open($File)
If($Workbook.Sheets.Item(1).Range("A:Z").Find($SearchText)){
$Workbook.Close($false)
$Excel.Quit()
$File.FullName | Out-File C:\Users\$Username\documents\test_folder\$Date.out.txt -Append
}
$Workbook.Close($false)
$Excel.Quit()
}
catch {
}
}
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)





A few things I should point out about the script.  I'm using "$env:UserName" but in certain situations, the username and the folder path will differ.  In my original script, I didn't run into an issue but when I attempted to use this on another machine, I ran into an issue with the domain being part of path versus the username.  For example:  the username is "user" but the path name is "user.DOMAIN".  This either makes sense or it doesn't but if you run into an issue with reading or writing, you can hard code the path instead of using a variable.

I also ran into an issue where the Excel process was getting stuck in the background.  When the script had finished, the processes were orphaned and when I opened Excel for another purpose, the orphaned processes were brought to light.  The last statement in the script kills any Excel process from this script that is running in the background. I was concerned that it might kill other Excel processes that were perhaps opened for legitimate reasons outside of this script but I tested it and that does not seem to be the case.  Buyer beware.

Aside from that, the only other thing I'd point out is that if the script comes across password protected documents, it will hang that document in the UI.  In other words, you should be using this for legitimate use because in most cases, users will typically password protect their password documents and you will alert them to something suspicious.  





I am well aware of the title of this post but it's much sexier than if we were to call it "PowerShell Boring Search Finder". 

Moving on...

After we execute our script, we get a unique output name from the $Date variable which is handy if we want to re-run this script:

When we open the text file output, we get two hits:

When we open the documents, we see:

And:

Now here's where the reality kicks in.  If you run this on a small set of files, you probably won't run into issues.  But if you run this on a large set of files, you are literally opening every file, searching through every file, and hunting for a phrase.  If there are numerous password protected documents, you're going to get hung up on each one. That's not to say there isn't a workaround for that but that's more code.  This is a starter.  In addition, if you think about how long it takes to use the Windows Search function on a large set of files, it's not fast and that is already indexed in most cases.  This is not indexed and it could take a long time to complete.  Finally, if documents are already open when you attempt a search, you'll get hung up there as well. Again, I think these issues can be dealt with accordingly but we're not addressing those issues with this script.