r/excel • u/Deep-Egg-6167 • Jun 25 '24
Employee left all files are password protected solved
Hello,
A client has an employee that recently left. All the files are made with 365 and are password protected. Is there anything that can be done to open them?
790
u/Over-Payment-5597 4 Jun 25 '24
As said before in the comments, it's a legal issue, but these things usually takes a lot of time so i will show how to unlock password sheets, not VBA passwords, for that you need to change HEX values in your file.
• Create a copy of the excel file, change it's extension to ".zip", if your files extension don't appear, search on google how to make it appear on you device OS, windows 7, 10, 11, mac, etc.
• Extract the zip and open main folder, open the "xl" folder and lastly "worksheets" folder. There you'll have .xml files.
• Copy these files to another place, open them with an text editor like the built in notepad for windows, click "Ctrl + F" to activate the search tool and search for "sheetprotection". There will appear something like:
<sheetprotection = true somtehing else=2, something else=True>
Select and delete the whole thing between the "<>" and include the "<>" to delete.
• Do it to each .xml file and paste them on the original folder.
• Go to main folder, zip it with winrar or 7-zip and change it's extension from .zip to the original extension, probably ".xlsm"
• open the new file you created, passwords have been disabled.
135
u/ConsiderationKey859 Jun 25 '24
Has this been patched? Trying to replicate this method and xl folder isn't visible in the zip file. The only folders are DataSpaceInfo, TransformInfo and 2 files DataSpaceMap and Version.
I am using Office 2016.
208
u/bradland 89 Jun 25 '24
There are two types of protection:
- Sheet protection
- Workbook protection
The above method only works for sheet protection.
28
u/LoganShang Jun 26 '24
Is there a solution for workbook protection?
218
u/bradland 89 Jun 26 '24
Nothing for the layman. Excel uses AES-256, which has no known vulnerabilities that allow you to bypass it. So your only attack options are brute force, ideally combined with dictionary.
The latter is what I would use. I briefly worked in computer forensics for an accounting firm that did work for bankruptcy trustees. I've broken into lots of encrypted files. The weakness is rarely the encryption; it's the user.
My favorite trick is to image the user's hard drives, use a Linux tool called strings to pull out all strings more than 4 characters long, then purge anything that is >25 characters. I then used that as my dictionary to brute force the file.
The crack rate for this technique was crazy high. People cannot resist writing down their passwords on their computer. Even if it's not something super-dumb like a spreadsheet full of passwords, they'll still frequently email the password to someone, or they'll send it on a messaging app that caches messages locally. The environment has gotten a bit tougher, but it's still a great technique.
76
u/heelstoo Jun 26 '24
Hmm. Time to change my password from “hunter2”.
77
u/releasethekraeken Jun 26 '24
Weird, all I see is "*******"
10
u/User-NetOfInter Jun 26 '24
Hijacking comment to say I am offering a new armor trimming service!!1!
7
7
u/IHaveThreeBedrooms Jun 26 '24
How many 4..25 char strings are there on a computer, ballpark?
13
u/bradland 89 Jun 26 '24
Oh man. So many. It’s been more than a decade since I had that gig, so I don’t remember line counts, but the dictionary files were hundreds of megabytes.
7
u/Dick_Souls_II Jun 26 '24
This is remarkably clever, yet simple process. Good reason to be using a password manager.
I have one question. For the strings between 4 and 25 that you would store, do you recall were they overlapping or non-overlapping?
2
5
u/frankfox123 Jun 26 '24
In college, a professor posted answer pdfs early, with a 3 letter password. After 3 weeks or so, answer pdf were still posted early but with a 24 character password :D
4
u/Astandsforataxia69 Jun 26 '24
AES-256
The second you manage to break that, FBI and NSA are going to be extremely intrested in your skillsets, and unfortunately for them, so are the rest of the intelligence agencies
2
u/sleverest Jun 26 '24
This is so simple yet genius. It also makes me a little proud of myself that my passwords wouldn't be found with this method.
39
u/RotianQaNWX 5 Jun 25 '24
Nope, still works on 2021 like nothing. Used it like week ago on .xlam and .xlsm file.
31
Jun 25 '24
[deleted]
36
u/Only_Positive_Vibes 10 Jun 25 '24
While true, it's probably fair to assume that OP has exhausted the option of "call ex-employee, get password" before asking Reddit for help.
51
Jun 26 '24
[deleted]
12
u/Only_Positive_Vibes 10 Jun 26 '24
Maybe I put too much faith in humanity...
17
u/mhyquel Jun 26 '24
Have you seen humanity recently?
4
23
u/arkofjoy Jun 26 '24
Sometimes it is incompetence on the part of the people firing the person.
I used to work at a school. The administrator of the school had poor social skills but was honestly doing the best they could. Board decided to fire her, and rather than let her work with her replacement, they had the board chairman walk her off the grounds. Except that she was the only one with the banking passwords and it took them 2 weeks to get possession of the accounts again. Thry almost couldn't do payroll. And after treating her with such disrespect, thry certainly weren't going to go ask her for them.
15
Jun 26 '24
[deleted]
18
u/arkofjoy Jun 26 '24
That is right. Had they instead treated her like a human, she would have gladly helped the new administrator.
Toxic work environments are expensive in ways that are largely immeasurable.
2
u/omniscientonus Jun 26 '24
I'll add another incompetence story. I occasionally do some contract work for an old coworker who started his own business. He mainly has me doing purchasing, and I keep trying to get him to sit down so we can communicate all of the accounts information, but he's always "busy right now".
Thankfully it's all just minor things that he uses a credit card for, but on several occasions he has had to contact me to get receipts and things he can't find. I keep asking him what his plans are if I were to get into an accident or something where I couldn't be reached. Soon he says. We'll see...
6
u/bobby429clearview 1 Jun 26 '24
Another method is to use an excel macro add-in. You will have to search for it, but since excel allows unlimited attempts, it just uses brute force to crack it.
3
u/Deep-Egg-6167 Jun 26 '24
Solution verified - Thanks - that method worked for me.
Sub Unprotect_Sheet_without_Password()
Dim x As Integer, y As Integer, z As Integer
Dim a As Integer, b As Integer, c As Integer
Dim x1 As Integer, x2 As Integer, x3 As Integer
Dim x4 As Integer, x5 As Integer, x6 As Integer
On Error Resume Next
For x = 65 To 66: For y = 65 To 66: For z = 65 To 66
For a = 65 To 66: For b = 65 To 66: For x1 = 65 To 66
For x2 = 65 To 66: For x3 = 65 To 66: For x4 = 65 To 66
For x5 = 65 To 66: For x6 = 65 To 66: For c = 32 To 126
ActiveSheet.Unprotect Chr(x) & Chr(y) & Chr(z) & _
Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & Chr(x3) & _
Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
If ActiveSheet.ProtectContents = False Then
MsgBox "Password is " & Chr(x) & Chr(y) & _
Chr(z) & Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & _
Chr(x3) & Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub2
u/reputatorbot Jun 26 '24
You have awarded 1 point to bobby429clearview.
I am a bot - please contact the mods with any questions
3
u/Deep-Egg-6167 Jun 26 '24
Thanks - I tried this but didn't have any luck but using the VBA worked for me
ub Unprotect_Sheet_without_Password()
Dim x As Integer, y As Integer, z As Integer
Dim a As Integer, b As Integer, c As Integer
Dim x1 As Integer, x2 As Integer, x3 As Integer
Dim x4 As Integer, x5 As Integer, x6 As Integer
On Error Resume Next
For x = 65 To 66: For y = 65 To 66: For z = 65 To 66
For a = 65 To 66: For b = 65 To 66: For x1 = 65 To 66
For x2 = 65 To 66: For x3 = 65 To 66: For x4 = 65 To 66
For x5 = 65 To 66: For x6 = 65 To 66: For c = 32 To 126
ActiveSheet.Unprotect Chr(x) & Chr(y) & Chr(z) & _
Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & Chr(x3) & _
Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
If ActiveSheet.ProtectContents = False Then
MsgBox "Password is " & Chr(x) & Chr(y) & _
Chr(z) & Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & _
Chr(x3) & Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub1
2
1
u/broodroostermachine Jun 26 '24
I have a macro for this to automate this proces and make a copy of the original file. Its not in english though, so i have to change some things to make it work for everybody.
1
u/babsiep Jun 27 '24
Is dit in Afrikaans?
2
206
u/bendorphin Jun 25 '24
Another user already gave the correct steps to depasswording excel files so I won’t repeat.
But worth exploring: if this was made in one drive you should have a version history on all files. Assuming the employee did this maliciously just prior to leaving, you should be able to find the point in time they opened the files + password protected them. Just revert to that version and save yourselves the headache of .zip etc every file.
If this employee actually did this to hamper progress, this is a cringe and feeble attempt to corrupt files. Truly, cringe.
108
u/delightfulsorrow 11 Jun 25 '24
Sounds more like a job for legal than one for IT.
63
u/huge_clock Jun 25 '24
One man’s r/excel post is another man’s r/pettyrevenge
24
u/bearfootmedic Jun 25 '24
You will win all the free points if you stumble upon the other side of the story
4
u/Puzzleheaded_Quail70 Jun 26 '24
Exactly what I was thinking, wish I had done this when leaving a previous job 😅
17
u/takesthebiscuit 3 Jun 25 '24
I don’t recall…..
-27
u/protostar71 Jun 25 '24
That's when you sue for the damages incurred by the locked spreadsheets they "forgot" the passwords too.
46
u/Jewnadian Jun 25 '24
Unless the guy did it the night before he was fired and then sent a message explaning his plan on the company servers that lawsuit is going nowhere. Password locking sheets is a very common thing in almost any business file. And forgetting a password is also very common.
3
u/shavedratscrotum Jun 26 '24
You just don't automate everything.
You leave 1/2 major and convenient parts of the process manual.
Then they have to pay you to consult back to maintain, and they have 0 claim.
You fired me for being incompetent. This only serves as furthe proof that was justified.
50
u/mystery1reddit 1 Jun 25 '24
Has anyone tried asking them ?
Not only will it be the easiest way, you'll quickly find out if it's deliberate.
25
u/welches420 Jun 25 '24
Yup! Though, that should have been addressed in the exit interview. Now he is a consultant haha.
11
u/DEFCON741 Jun 25 '24 edited Jun 26 '24
Something tells me this was done intentionally....and OPs client now has tail between legs lol
33
u/Illustrious_Pool_198 6 Jun 25 '24
Was the password intentional or just for security. If it was on purpose, i would not touch the files since the data may also be corrupted in some form or may not be of use.
38
u/Doctor__Proctor Jun 25 '24
And actions like this are why so many places terminate immediately. They just don't think it's worth the slim chance someone will try something like this.
-1
u/Doctor__Proctor Jun 25 '24
And actions like this are why so many places terminate immediately. They just don't think it's worth the slim chance someone will try something like this.
19
u/OddWriter7199 Jun 26 '24
Uploading to Google sheets removes the password, read on another thread in r/excel recently. https://www.reddit.com/r/excel/s/3bCmlUstpq
10
u/Squischmallow Jun 26 '24
Yup, google sheets does not honour any protections from excel
11
u/MarcieDeeHope 4 Jun 26 '24
It does if they are on the workbook. For passwords on individual sheets this works, but if you have an Excel workbook with a password just to open the workbook and you open it in Sheets, Sheets will ask for the password. I just tried it on both my work and personal accounts.
1
0
23
u/akaenragedgoddess Jun 25 '24
Vba code to get a password that works to unlock sheets: https://www.instructables.com/VBA-Code-To-Unlock-A-Locked-Excel-Sheet/
14
u/NinjaAffectionate128 Jun 25 '24
Download a copy of Apache's open office. Open the file with open office and save it as a .ods extension. It will at some point prompt you about passwords, and you can elect not to keep them.
Go to the .ods file and open it with Excel version whatever. The file will be free of passwords.
If the file has macro functionality, the vba will not carry over so this only works on simple workbooks and sheets.
10
u/cbapel Jun 25 '24
Consider this a great wake up call that you need a better backup strategy. You'll probably manage to fix this relatively easily, invest to make this a simple restore problem.
10
u/cocobananas_ Jun 25 '24
I was able to save a 365 password protected doc as an Excel 97-2003 Workbook and it removed the password 🤷
2
8
u/Shurgosa 4 Jun 26 '24
If they are protected in a way where you cannot even open and view the file without the password, then they cannot be cracked open with any sneaky hack.
I've been begging anyone to prove me wrong on this, and so far nobody has presented any kind of fix. All anyone ever talks about is when a tab inside a spreadsheet is locked from being edited..
7
7
u/Open_Bug_4251 Jun 26 '24
Are you sure there’s an actual password? I have all of my worksheets protected, but the password is blank.
3
2
u/defcry Jun 26 '24
Just an idea, but if you use sharepoint you may be able to check the history and different snaps from the past and maybe some of the files are unprotected.
1
u/Gettitn_Squirrelly Jun 25 '24
Had this happen to me, I tried every method I could find on the internet. Nothing would work, it was an office 2021ish so I think it’s encrypted. Only method I didn’t try was the websites that basically keep trying combinations until they find the password.
1
u/HalfRepresentative27 Jun 26 '24
Had that issue with Excel and PPTs of a former employee. Files were needed within a week. Used a bruteforce attack that took ~30h per file.
1
u/Actual_Variation_166 Jun 26 '24
Simple hack you can try (it somehow worked for me once)- go to google sheets web and import or open the file here. Boom now the file is password free. Do let me know whether it works for you or not.
1
u/bluepost14 Jun 26 '24
Try uploading to google drive and open in google sheets. I’ve seen excel files completely bypass encryption and open right up (good in this case, bad for if you really want to protect it)
1
1
1
1
1
1
u/Lazy-Environment7669 Jun 28 '24
If he resigned ..call him and ask the password...if you fired him...screw yourself
1
0
u/BraisedUnicornMeat Jun 26 '24
DM me. Ill send you a VBA to run that unlocks
1
u/Deep-Egg-6167 Jun 26 '24
Thanks - I got it.
ub Unprotect_Sheet_without_Password()
Dim x As Integer, y As Integer, z As Integer
Dim a As Integer, b As Integer, c As Integer
Dim x1 As Integer, x2 As Integer, x3 As Integer
Dim x4 As Integer, x5 As Integer, x6 As Integer
On Error Resume Next
For x = 65 To 66: For y = 65 To 66: For z = 65 To 66
For a = 65 To 66: For b = 65 To 66: For x1 = 65 To 66
For x2 = 65 To 66: For x3 = 65 To 66: For x4 = 65 To 66
For x5 = 65 To 66: For x6 = 65 To 66: For c = 32 To 126
ActiveSheet.Unprotect Chr(x) & Chr(y) & Chr(z) & _
Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & Chr(x3) & _
Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
If ActiveSheet.ProtectContents = False Then
MsgBox "Password is " & Chr(x) & Chr(y) & _
Chr(z) & Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & _
Chr(x3) & Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
0
u/Haplo12345 1 Jun 26 '24
Sue them for access. It's legally the company's property and they are withholding access from the company. If they can't provide access, sue them for damages.
-1
u/rtmondo64 Jun 26 '24
Call the employee, ask for the password. If there is possible litigation for malice, this will allow him/her to show no malice by providing password. Yet, also their voluntary conveyance will limit recovery (which would be minimal anyway) should he/she have intentionally locked the files with the intention to harm the employer. Lawyers won’t like this option….
8
u/Optimal_Law_4254 Jun 26 '24
Saying no or not responding doesn’t prove anything about whether the files were passworded maliciously. It’s the exact same thing as other pieces of tribal knowledge. If you’re a jerk to the only person who knows something critical to your business and you fire them don’t expect them to even talk to you later.
5
u/shavedratscrotum Jun 26 '24
You just don't automate all your sheets.
Leave some small but critical part manual with 0 instructions.
Then charge consult fee ongoing.
3
u/Optimal_Law_4254 Jun 26 '24
That would be more questionable intent.
I was talking about the many other reasons why a file could be locked or encrypted without any malicious intent at all. We had critical Excel workflows that people would screw around with and break and then they would play dumb. It would take hours to track down the problem and fix it. Files got locked.
1
u/shavedratscrotum Jun 26 '24
Yeah I understood that, I did the same.
Only 2 cells need manipulation and they'd still fuck them.
1
u/rtmondo64 Jun 27 '24
I’d get your point if the employee was fired. He left voluntarily. Also, there’s nothing here to indicate the employee was treated poorly. People turn over in their jobs all the time. I’ve always maintained a respectful relationship with my team and when they had an opportunity that I couldn’t match, I wished them well and will be a reference if ever needed. Yes, I’ve had a few bad hires but I could tell within 3-4 months; so they’d never have this level of access. I understand the Reddit bias that all those in charge are jerks. I’m not, nor are my peers at my company. I’m just saying, pick up the phone. What do you have to lose? Other than the few downvotes…
1
u/Optimal_Law_4254 Jun 27 '24
I hear you. Reddit bias is absolutely what you describe.
I was more responding to the posters comments about proof of intent and others that assert there’s some kind of obligation to come back and help them for free.
If you were my boss and we parted amicably and you called me for help you would probably offer to take me out for a nice steak dinner if I could come by after work and help unlock a few files. 😁.
2
0
u/DeeFeS Jun 26 '24
Saying no very much does.
If a carpenter were to lock all the drawers of a closet they were employed to build, take the keys and refuse to give them up, that's intent.
And I would expect to be talked to again in court if I were that employee.
3
u/Optimal_Law_4254 Jun 26 '24
That’s apples and oranges. Your example does go to intent.
Some people don’t know how to share files with some people and not with others so they encrypt them and leave them in a share. Some people lock an excel document to keep people from accidentally changing things they shouldn’t like vba or worksheet formulas and formatting. It’s routine with no intent whatsoever.
Then the person is fired. That ends their obligation to the company unless specific laws or contracts apply. You want anything other than the return of physical company property? I’m willing to consult at an appropriate hourly rate. I’m not obligated to do free consultations to pass on information.
0
u/DeeFeS Jun 26 '24
Idk in which country you're living, but where I'm from the fruits of your work are company property. That's what you get paid for.
It's your obligation to make your work which they paid for accessible to them, especially if your settings are what causes delay or general damage to the company in the first place.
The idea that you get paid as a "consultant" for unlocking files you already got paid to create in the first place is proposterous.
In my country you will either be mandated to open these files or sued into oblivion if you try to pull this crap.
3
u/Optimal_Law_4254 Jun 26 '24
So more context. If I have already done that or gave them every opportunity during my employment then I don’t have to have uncompensated training for them after I’m terminated.
I think people are getting all upset because they think this is some sort of revenge plot. Those are illegal as I’ve said before. That’s not what I’m talking about. I gave examples in other responses.
I’ve been in IT for decades and have had dozens of clients. I’ve never been sued.
1
u/DeeFeS Jun 26 '24
That's not at all what we're talking about. OP has password protected files that a former employee created and didn't leave them in a usable state for the company.
Comparing that to a system no one is trained in, is comparing apples and oranges.
1
u/Optimal_Law_4254 Jun 26 '24
I also commented about how it depends on the circumstances and whether or not you’re bound by terms of a legally enforceable contract or other laws.
1
u/DeeFeS Jun 26 '24
If there was no legally binding contract that states that the former employee produces work for the company than the entire argument is indeed void, but in my understanding, such things are implicit when you call people employer and employee.
-6
u/subjecttomyopinion Jun 25 '24
There's definitely software out there that will crack vba passwords. I can't remember the name but I grabbed a trial of one and it busts anything open.
1
-5
u/Maligater Jun 26 '24
Ask your 365 Administrator to unlock the account. Then you can do everything as if you were the person. Active Directory may also be an option if the account was attached to SSO.
3
•
u/AutoModerator Jun 25 '24
/u/Deep-Egg-6167 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.