Ricochet is the best place on the internet to discuss the issues of the day, either through commenting on posts or writing your own for our active and dynamic community in a fully moderated environment. In addition, the Ricochet Audio Network offers over 50 original podcasts with new episodes released every day.
How to Crack Excel Files
This all starts with Mike Mahoney. Mahoney was the Excel guy, two Excel guys ago. To his credit, he wrote pretty good stuff. His macros don’t break often. Everything would have been cool except he was writing these things when Excel 2003 was the hot new thing. Mahoney was also excellent about locking things down from accidental damage. Trouble is, nobody remembers his passwords. Breaking through his protections makes an excellent case study on how to secure and how to bypass the security on an excel workbook.
Not even swordfish works.
The guy on the floor gives me a call. I don’t know the guy; IT gave him my name. They don’t support random excel macros, so I get these calls. His macro works fine on most computers, but it errors out right away on one. I’ve never seen this particular spreadsheet before, but I’ve got a pretty good idea why. His new computer is 64 bit, which messes with some function calls.
Okay, let’s go in and fix it. I make a local copy. Don’t want to mess with the one people are using; leads to trouble. Open it up. Before it even loads it’s asking me for a password. I don’t have one. I’ll open it as read-only for now; I’ll show you how to fix that later. For now, this is what I see:
I don’t think y’all can do anything malicious with that network location, but I ain’t takin’ chances.
What’s going on here? Mahoney is trying to make sure nobody’s using local copies. Whenever someone opens up the workbook, it looks for its location on the network. If it isn’t in a specified folder, lock the sucker down so that nobody can use it. This seriously helps when you’ve got to make sure everyone’s using the same version of a file (fix a bug once, fix it every time. Update control limits once, don’t worry that someone’s checking pass/fail against an outdated standard). It’s annoying when you’ve got to mess with the file yourself.
Okay, let’s bypass this. He’s got one tab (“Default”) showing now. I know there’s more to this workbook as it’s supposed to do more things than taunt the hapless user. If you try to unhide the tab though, you don’t see any.
Excel actually has three levels of visibility for worksheets. You’ve got visible, hidden, and very hidden. Hiding a worksheet is something you do when you want to get it out of your face for a while. Making something very hidden means that people can’t even see that it’s there. Unless they’re going through the visual basic project.
To show all hidden and very hidden sheets, this is the code you use:
Sub Sistence
‘show all hidden worksheets in the active workbook
Dim wSheet as worksheet
For each wSheet in ActiveWorkbook.Worksheets
wSheet.visible = xlSheetVisible
Next ‘worksheet
End Sub
If you’re trying to hide sheets use xlSheetHidden and xlSheetVeryHidden instead. If you try to hide every sheet you’ll get an error when you go to hide the last one. A workbook has to have at least one sheet visible.
Okay, all the sheets are visible. Let’s get rid of that pesky open-as-read-only thing. You can do that from the Save As command.
Enter it. I dare you.
Wait, what? Mahoney has set up another event. This one triggers before you save the workbook. It demands a password, and if you can’t supply then it cancels the saving. If I can’t get past this I can’t ever save changes and hence I can’t fix the problem. I still don’t have any passwords.
An “event” is a trigger that waits for something specific to happens. When that something happens the event handler comes in and says “hold on a minute; I gotta do something.” Mahoney’s password box triggers before a workbook is saved. That thing where it hides all the spreadsheets? That’s an event that triggers when the workbook is opened.
Here’s how you shut off event triggers:
Application.EnableEvents = False
You can do that from a macro, or from the immediate window. Once you’ve done it, you can get into the usual Save As dialogue box. It needs to be Save As because we opened this thing as Read Only a dozen paragraphs ago.
As you might have guessed this is the file where I save these tools.
While you’re here, note the Save As Options menu (yeah, I never noticed it either before it was pointed out to me). In general options, you can put in a new password. You don’t even have to know the old one. Great! Save your file under another name, close out, and open your new file to check it.
Good news, it’ll let you make changes. Bad news. We’re back to that “Opened from the wrong location” thing. Events turned back on when we opened it again, and it found it’s in the wrong folder. Okay, no matter. We know how to get around that now.
One side note before we go on. Suppose we needed to make a change to this worksheet. Can’t do it; it’s protected. To unprotect a worksheet, you guessed it, you need a password that I don’t have. To get that you’ve got two options.
First option, you go into the macro and see if you can’t find the password. If your macro ever writes new data to a protected cell then it’s got to unprotect it first, write the data, and protect it again. You’ll find the password supplied somewhere in the code. Supposing you don’t have that though. Supposing that the visual basic project is locked out, or it never unprotects that worksheet. In that case, you can call upon a classic password cracker:
Neat, huh? You don’t need the actual password, you just need one that works. For reasons that I’m not clear on you can generate a password that also unlocks it from a number of possible solutions. It’s a small enough number that you (well, your computer) can try them one by one in a reasonable amount of time. Last time I did it the password worked out to something like AAABBBAAABA; . It’s quicker to find if you know the first eleven characters have to be “A” or “B”.
Alright, now here’s the big one. Mahoney locked down the visual basic project. If I can get into the visual basic project then I’ve got access to everything. If I can’t, then I can’t fix this guy’s problem. Man, that Mahoney fellow owes me a beer.
To break past the Visual Basic security you’ve got to mess with the underlying Windows system. There’s a way to do this that involves renaming the file as a .zip, opening it up, and messing with one of the xml files inside. I find this solution to be easier. Stick this procedure into another workbook. From there, and with your locked workbook open, you run the macro. In the simplest sense that turns the key on the project being locked. It no longer is. If you save, close and open it up again it’ll be locked down again, and you still won’t know the password. From there you’ve got to go into the Visual Basic Project Properties and reset the password. Hopefully to something you will remember, if not pass on to the guy two down the line. Once you’re in the visual basic you’ve got everything. If anything else is hidden you can find it and break into it as necessary.
What then would I do to secure a workbook of my own? Supposing I wanted to lock something down to the point where an enterprising moron such as yours truly won’t get in and tinker with it. Here are the steps I would take:
- Disable the user’s ability to save changes to the workbook.
- Set the workbook to only run from the specified directory.
- Protect any worksheets they don’t explicitly need to change.
- Password protect the VBA project.
In short, I’d still go with the Mahoney method. The fact of the matter is I don’t know of a better way to secure your work while it’s still in Microsoft Excel. It takes time and effort to break through all those protections. It’s very hard to fight through that by accident. But I’ve got no way to stop a malicious moron from breaking into any old workbook. The lesson here ought to be not to trust your sensitive business logic to Excel. Somehow that never seems to be the lesson learned.
In the meantime though, I can give the newly repaired file back to the guy on the floor. Wait, what do you mean that fix didn’t work?
Taken from xkcd.com
Yikes!
I was never the Excel guy. I was the IT guy telling people we don’t deal with rando Excel files.
You’re welcome.
If I’d known any of this way back when I had minions, I would have known how to ask for a worksheet that said world-wide minions could not “fix…..” and create competing “solutions” to problems. Except that – everything between the first and last sentence @hankrhody was in something like Klingon. You engineers continue to impress with clever. Thanks!
I’ve gotten into some trouble creating my own solutions to problems. Generally though the macros I’ve written were solving problems that we didn’t have software for.
We get metal in these great big rolls of stainless steel. Orders are split off those rolls. Occasionally we want to track the individual order back to the roll it was split off of (potential defects in the incoming material and such.) I put in a request to get it tracked. The guy on the floor already has to scan that barcode, so the machine knows about it. It just doesn’t store the information anywhere.
I put in a request to get them to track that. Just track that string in the database, you’ve already got a field for it. They sat on it for six months and said “can we close this already?”
I took an afternoon and I worked up a solution in excel. It queries the local database, scans through the event log for things that say “you scanned this order with this metal lot”, and puts those into a spreadsheet somewhere acting as an ad hoc database.
It’s a terrible solution for several reasons, but it works, and it was quicker and easier than browbeating my way through the bureaucracy.
I got no problem with that. I’m fine with “your program your problem”, but when it’s “some other guy’s files, good luck with that!” it can get frustrating.
Tags don’t include, “Tales From The Coders’ PIT?”
I used to think I was good at this stuff–well, “good” compared to other arts majors who graduated 45 years ago–but reading Hank’s post makes me feel like Penny in The Big Bang Theory.
Wow! That was kind of intense.
I’ll have to remember this for future reference.
Oh don’t worry. I didn’t invent most of this stuff; I copied off of the other students.
That guy’s spreadsheet is still busted, by the way. Going to have to take another look at it when the holiday wears off.
He has “Thunderdome.” Same t’ing!
Main Feed promotion is an interesting process.
Ha! You hit home with that remark. Exactly how I feel reading anything about subatomic particle physics these days – from one who remembers trying to determine particles from their paths on the old time cloud and spark chamber pics on physics exams
Sounds fun.
Fun??? No so much…
I studied physics the first time I went to college. Got far enough that I saw those cloud chamber pictures, never had to identify the particles on them. Our school wasn’t that big on particle physics.
I spent my career as a financial analyst at a major defense company (think missiles/Tucson) and I was amazed at how much of our work depended on ad hoc macros written by brainy new college graduates who went on to bigger and better things after a year or two, never to be heard from again. The macros would work but we didn’t know how (we’re finance people, not IT!). After a couple of years written task instructions would include phrases like “run Macro A, copy results to new sheet, delete row 10, finish by using pivot table” . At least most of the missiles worked. I think.
Awesome post. I would never have thought reading an article about screwing with an excel spreadsheet would be so interesting.
I think it’s the illicit nature of breaking into things. A discussion of banking regulations is dull. A discussion of how to pilfer regardless, you have my attention.