Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

How to batch remove spreadsheet password

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:
http://superuser.com/questions/572171/remove-password-from-excel-files-in-batch
====================================
Test DocRecrypt.

Microsoft Office 2013 DocRecrypt Tool

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

DocRecrypt –i lockedfile
===================================
0
RickNCN
Asked:
RickNCN
  • 12
  • 5
  • 2
2 Solutions
 
Giovanni HewardCommented:
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"

Open in new window


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"

Open in new window


If you want to use these commands in a batch file, you'll need to substitute %f with %%f.
0
 
RickNCNAuthor Commented:
Yes, I do need to traverse folders I believe. Ok, I'll try #2 and see how it goes. Thanks
0
 
RickNCNAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
RickNCNAuthor Commented:
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
 
RickNCNAuthor Commented:
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
 
Giovanni HewardCommented:
Try using Passware Recovery Kit.

Details on its Excel capabilities are listed here.
0
 
RickNCNAuthor Commented:
That doesnt look like what I need, it's for recovering passwords or finding encrypted files.

Is the docrecrypt possibility not viable?
0
 
Bill PrewCommented:
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
 
RickNCNAuthor Commented:
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
 
Bill PrewCommented:
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
 
RickNCNAuthor Commented:
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
 
RickNCNAuthor Commented:
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
 
Bill PrewCommented:
Okay, I'm working up a test to see if we can make some headway...

~bp
0
 
RickNCNAuthor Commented:
wonderful!
0
 
Bill PrewCommented:
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")
arrPassword = Array("", "p0", "p1")

' Instantiate the Excel application, but don't show it
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = 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
   For Each strPassword In arrPassword
      Set objNew = objExcel.Workbooks.Open(strFile,,,,strPassword)
      ' No error, this password worked, save it and exit loop
      If Err.Number = 0 Then
         strOpenPassword = strPassword
         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

Open in new window

***** 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

Open in new window

~bp
0
 
RickNCNAuthor Commented:
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
 
Bill PrewCommented:
No, you do not want the line numbers.  Here it is as an attached file as well...

~bp

EE28982397.vbs
0
 
RickNCNAuthor Commented:
I'll be trying this next Monday and will reply.
0
 
RickNCNAuthor Commented:
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

Managing Security Policy in a Changing Environment

The enterprise network environment is evolving rapidly as companies extend their physical data centers to embrace cloud computing and software-defined networking. This new reality means that the challenge of managing the security policy is much more dynamic and complex.

  • 12
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now