Solved

Posted on 2016-11-10
140 Views
How do you go about removing the password on hundreds of xls files? The password, I think is the same for all. This answer was posted on the following site:
====================================
Test DocRecrypt.

Microsoft Office 2013 DocRecrypt Tool

To remove the password from a file, use this code:

DocRecrypt –i lockedfile
===================================
0
Question by:RickNCN
• 12
• 5
• 2

LVL 14

Assisted Solution

Giovanni Heward earned 50 total points
ID: 41883771
Assuming all excel files are located in the same folder, with the same password, you would use the following command line:

for %f in (*.xls?) do @docrecrypt -p password -i "%f"


If you need to traverse subfolders, you'd use the following:

for /f %f in ('dir /s/a-d/b *.xls?') do @docrecrypt -p password -i "%f"


If you want to use these commands in a batch file, you'll need to substitute %f with %%f.
0

Author Comment

ID: 41886846
Yes, I do need to traverse folders I believe. Ok, I'll try #2 and see how it goes. Thanks
0

Author Comment

ID: 41887091
I used the command line you gave and got an error. What I did was copy DOCRECRYPT.EXE to the folder where all the Excel files are. I opened a command prompt from that folder (Open Command Window Here) and ran the line you gave.  This is what resulted:

D:\Users\Rick\Documents\!XXXX COMPUTER FILES\Customers\XXXX\Excel Formulas Pricing test files\354>for %f in (*.xls?) do @docrecrypt -p <password inserted here> -i "%f"
Input File:354-179.xls (inplace) Error: Error_StreamRename Last Result: 0x80030002
Input File:A354-007.xls (inplace) Error: Error_StreamRename Last Result: 0x80030002....................etc

I'm sure I did something wrong. Any idea?
0

Author Comment

ID: 41887100
Well, I thought the errors meant that it didnt work, but it looks like it did - at least for some. It seems that some files' passwords were not what I thought. So it was successful for all the files that had the one password I put in.

So that begs the question, what do I do, just run multiple passes with the known passwords for the other files? or is there a way for the command line to try different passwords? I'm guessing no.

Then the next question after that is, any quick way to know whether all the passwords have been removed? In other words, if there are 1000 xls files, 250 with password1 and 500 with password2, what's an efficient way to check to see if all files have had their passwords removed?
0

Author Comment

ID: 41887130
Ok, well now I'm confused. I'm not sure any passwords were changed. If I have a pool of several different passwords that might be correct, what do I do? This may be the case across all the thousands of files.
0

LVL 14

Expert Comment

ID: 41887162
Try using Passware Recovery Kit.

Details on its Excel capabilities are listed here.
0

Author Comment

ID: 41887219
That doesnt look like what I need, it's for recovering passwords or finding encrypted files.

Is the docrecrypt possibility not viable?
0

LVL 52

Expert Comment

ID: 41888234
There seem to be some requirements for the DocRecrypt tool, have you met these?
You won't be able to use the DocRecrypt tool to remove the password from just any Microsoft Office document. For the tool to work, an administrator must have the private key to the escrow certificate, and the document must have the escrow key option enabled.
Also, the -p parm is not the existing password to the document, but rather a new password to assign to the doc after the process executes.  The idea of this tool is that you do not need to know the existing password of the file.

As a test, I would also try using both the -i and -o parms, sending the output to a new file.  Much safer that way anyway, and might help with the access problem you are having.

Have you gotten the tool to work for a single file from the command line?

~bp
0

Author Comment

ID: 41888700
For the tool to work, an administrator must have the private key to the escrow certificate, and the document must have the escrow key option enabled.

Technet says:
By using Office 2013 and an escrow key, which is generated from your company or organization’s private key certificate store, an IT admin can “unlock” the file for a user and then either leave the file without password protection, or assign a new password to the file. You, the IT admin, are the keeper of the escrow key which is generated from your company or organization’s private key certificate store. You can silently push the public key information to client computers one time through a registry key setting that you can manually create or you can create it through a Group Policy script. When a user later creates a password-protected Office 2013 Word, Excel, or PowerPoint file, this public key is included in the file header. Later, an IT pro can use the Office DocRecrypt tool to remove the password that is attached to the file, and then, optionally, protect the file by using a new password.

So, This company has no private key certificate store that I know of. And, these files were created without one to begin with, I think...

If I get this to work, then in the password removal process, I need to identify which password out of several was used to open it, and reapply that password back afterwards.

I may need an Excel expert to work remotely on this problem. I see EE has this 'gigs' feature. Do you think that's a better way to go?
0

LVL 52

Expert Comment

ID: 41888733
Before I suggest how to  proceed, can you give me a little more context?  What is the motivation for removing all the security on these hundreds of files, what is the situation with them, etc?  Are the files just protected with an open password, or a write one too, are they the same?  Any other "protection" on the files other that that?

So the end result is you want no protection on these files?

We could probably do some VBScript automation to Excel to try opening each file with each of a predefined list of possible passwords if you think you know most of them.  That wouldn't be that hard to do.  Ideally having a sample file to work with would be the fastest path, hard to debug code without real data, but if there is sensitive info in the files then that may not be possible.

~bp
0

Author Comment

ID: 41889803
Thanks, Bill,
yes, I should have given some context. So here's the deal:
These Excel files are proprietary chemical formulas for this company. They have been created and used over the past 20+ years - probably starting around Win95 days. As far as I know, there are only open passwords, not write passwords. There are probably at least 3 different possible open passwords, maybe slightly more. The problem is that all of these Formulation Spreadsheets rely on a master Raw Materials Database which holds cost information for hundreds of discrete chemicals. The Formulation Spreadsheets have the recipe for the formulation, but in order to come up with a cost and sales price, that sheet looks to Raw Materials sheet for current materials costs using a VLOOKUP function. In the past year, something happened to corrupt the functions in the FORMULATION sheet and they no longer reference RAW METERIALS.xls for costing data. I've figured out at least 2 different "incorrect", corrupted functions, and know the correct strings to replace them with so they work.

So the repair of the problem is essentially easy - I can do a manual "Find and Replace" and fix individual files, but there are too many to do manually. I need an automated Find and Replace, which I've found: (Multiple Search and Replace (http://4dots-software.com/multiple-search-replace/). But before I can use that, I have to remove the open passwords. Then, after repairing, I need to reapply the password that was removed. (I *think* - If the owner agrees, maybe I can put just one password on all the files).

Does that make sense?
0

Author Comment

ID: 41889924
I spoke with the owner and he confirmed it's ok if I replace only one password on all the files. That makes this a good bit simpler.
0

LVL 52

Expert Comment

ID: 41889988
Okay, I'm working up a test to see if we can make some headway...

~bp
0

Author Comment

ID: 41890016
wonderful!
0

LVL 52

Expert Comment

ID: 41890061
Okay, let's try a small proof of concept against some of your files before we go too far.  This isn't elegant, but if this doesn't work no point going too much further until it does.

Save this as a VBS file.  Then create a small test directory and copy half a dozen or so of your excel files that are protected into that test folder.  Update the VBS file to reference the folder where the test files are (only excel files in that folder please), and also change the password array to have the guesses you want to use.  Leave the first entry as blank so that is a file is not protected we will know that.

Open a DOS command prompt, and run the VBS script as follows, and see what output it produces.  Below is a sample of the test output I got here on three excel files, one where there was no password, one with a password in my list of guesses and one with a password not in my list.  Hopefully you get the idea, the output indicates if it could open the file, and if so what password worked in parens.

***** SCRIPT *****
' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Define folder to process and passwords to try
strBaseDir = objFSO.GetAbsolutePathname("B:\EE\EE28982397\in")

' Instantiate the Excel application, but don't show it
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False

' Access folder of files, process each one
Set objBaseDir = objFSO.GetFolder(strBaseDir)
For Each objFile In objBaseDir.Files

' Define paths to CSV and Excel file paths
strFile = objFile.Path

' Try to open using each password
On Error Resume Next
' No error, this password worked, save it and exit loop
If Err.Number = 0 Then
Exit For
End If
Err.Clear
Next
On Error Goto 0

' Were we able to open the file, report results
If objNew Is Nothing Then
Wscript.Echo "*FAILURE* " & strFile
Else
Wscript.Echo "*SUCCESS* " & strFile & " (" & strOpenPassword & ")"
objNew.Close
Set objNew = Nothing
End If

Next

' Shut down Excel
objExcel.Quit

***** MY TEST OUTPUT *****
*SUCCESS* B:\ee\EE28982397\in\f3.xlsx ()
*SUCCESS* B:\ee\EE28982397\in\f1.xlsx (p1)
*FAILURE* B:\ee\EE28982397\in\f2.xlsx

~bp
0

Author Comment

ID: 41890151
wow! great! I will try it. I can't do it until later this afternoon. I have the general idea on how to run this script, but when I copy it to make the VBS file, should the line numbers be copied as well? Because they aren't selecting. I'm ignorant about most of VB scripting.
0

LVL 52

Accepted Solution

Bill Prew earned 450 total points
ID: 41890253
No, you do not want the line numbers.  Here it is as an attached file as well...

~bp

EE28982397.vbs
0

Author Comment

ID: 41899708
I'll be trying this next Monday and will reply.
0

Author Closing Comment

ID: 41899796
The VBscript seems like it will be the answer. I can't try it until at least next Monday now with the holiday. I will hopefully post the results here. docrecrypt wasnt a method for removing a known password, that was for unencrypting files that you have the certificate for.. I believe.  I divvy up points based on  column-inches of input, and how much time was put into the answer.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Delete texts with font color 16 29
Excel 2016 - Black cell borders 11 26
Excel case statements 3 24
Excel 2016 formulas 5 22
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How important is it to take extra precautions to protect your online business? These are some steps you can take to make sure you're free of any cyber crime.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…