Solved

How to batch remove spreadsheet password

Posted on 2016-11-10
19
92 Views
Last Modified: 2016-11-23
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
Comment
Question by:RickNCN
  • 12
  • 5
  • 2
19 Comments
 
LVL 14

Assisted Solution

by:Giovanni Heward
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"

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
 

Author Comment

by:RickNCN
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

by:RickNCN
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

by:RickNCN
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

by:RickNCN
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

by:Giovanni Heward
ID: 41887162
Try using Passware Recovery Kit.

Details on its Excel capabilities are listed here.
0
 

Author Comment

by:RickNCN
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 51

Expert Comment

by:Bill Prew
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

by:RickNCN
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 51

Expert Comment

by:Bill Prew
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

by:RickNCN
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

by:RickNCN
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 51

Expert Comment

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

~bp
0
 

Author Comment

by:RickNCN
ID: 41890016
wonderful!
0
 
LVL 51

Expert Comment

by:Bill Prew
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")
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
 

Author Comment

by:RickNCN
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 51

Accepted Solution

by:
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

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

Author Closing Comment

by:RickNCN
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now