saving xlsm workbook as xlsx protects the wkbook??

[foxtrotter]

Hi, I'm using a software system for auditing. One feature that is nice is they have a library of workpaper templates. I found out how I can add my own custom templates to the folder for quick access in the software application (very user friendly), which is what my manager tasked me with! Hurray. Only one problem. She liked one of the templates in particular (analytical procedures workpaper template for all you auditing nerds) and so I made that template and beefed it up. It runs as a macro enabled workbook. My workbook opened fine but hers she had to enable permissions, otherwise she only got one tab. I'm still pretty new so I couldn't figure it out and besides I want to make this most user friendly. Also the xlsm takes time every time I hit save, to go through every tab. It's annoying. It's not a super complicated workbook- just some tables and forums, pretty ordinary, so I wanted to save it as an xlsx and put it in the template folder, which I did and everything was going surprisingly well and I was about to boast to her of my completion- she's out of the office today and she's also the reason I got hired. She fought for me. This is kind of work before the busy season, the calm before the storm where not a lot is expected and I want to come through.

So the problem is when I save it as xlsx from xlsm, the workbook looks good and I can access it as I want, but all the sudden the workbook gets protected and I can't unprotect it with a password. Moreover, I can't access the VBA even in the xlsm file without a password. Is there a macro that someone programmed that told it to protect the workbook if someone saves it as another format, and is there any way around this? I'll try cut and paste. That might work but this is as much a curiosity question as anything. I can't access the macro code and I can't edit the workbook when not saved as a macro but the macro at this point is annoying.

Thanx!

[Zer0Cool]

No idea about the software system your using. What I can say is this

A xlsm contains macros and a xlsx does not. If the xlsm file had any code in it, it is stripped out when saved as xlsx. This doesnt mean you cannot run code on a xlsx file, only that its not self contained (ie the code would need to be saved in another workbook or addin).

When you say the workbook gets protected, how? Is it a password to open, a password to edit, sheet protection or workbook protection (as if applied via Review | Protect Workbook)?

[foxtrotter]

Interesting. That answered a curiosity question I had from an earlier day (self containment of macros and running code in non xlsm files). That makes sense. Every day learn many new things and ways of thinkin and understanding things.

Ok so saving as xlsm, obviously the code is stripped, yet when I am inside it as an xlsm, I can edit all the cells. When I save it as xlsx, I can edit hardly anything, column widths included. I can add new tabs and edit them. I managed to copy all the pages into a new workbook, so I can access everything as desired and use it macro free, so substantively, I am fine, but curiosity wise and for future's sake, I think that saving it as xlsx triggers a macro to run that password protects the entire book, as kind of a poison pill or whatever they call it. It makes sense from the proprietary aspect of the file- at least in theory. Shoot, 5 days ago, I hardly new what protecting a workbook was but I did it myself just a few days ago. I love learning.

Yeah because when I write code in my personal workbook (which always opens by the way, whenever I open any other workbook, and I Can't seem to hide it, but that's ok- yeah I can run that code in any other workbook, whatever the extension. I don't know that much about creating local file macros (macros assigned to just that workbook) but we're getting off topic. For some reason I think my system is different. The whole engage application used to not accept my macros from my personal sheet, at all, even when it was open. I ended up taking someone's advice on a forum by saving as an ad-in, and I'm still not very conscious of what I did, but it seemed to work, and now the excel files opened from the engagement software always run my personal workbook macros, even when it's not in the xlam project but the xlsb one. I can add buttons to the ribbon too and keyboard shortcuts. What fun!

[Zer0Cool]

So there are basically 4 types of protection possible on an Excel file:

• File level. Save As | Tools | General. Gives a password to open the file and password to edit. This encrypts the file.
• Sheet Protection. Review | Sheet Protection. prevents unwanted changes at the sheet level. Alter a cell, change a column, add a row, etc. Applied to each sheet independent of the others. Not encrypted.
• Workbook Protection. Review | Workbook Protection. Poorly named. Protects the structure of the workbook, ie view and window size, not the contents. Not encrypted.
• VBA project protection. Protects your code from being opened and viewed without password. Not encrypted.

Most people call file level "Workbook protection" while technically the 3rd option is called by MS "Workbook Protection".

Yes its possible a macro can do either sheet or workbook, not sure about file level (although probably). So maybe their macro forces some protection on the file when saving as xlsx. Not sure the benefit as the code is lost if saved as xlsx anyway and worksheet and workbook protection is very easy to circumvent (I can unlock either in under a minute with a text editor regardless of password complexity).

As for macros, they typically reside in 1 of 4 places. In the file itself (module, form, workbook, worksheet or class module), in another file, in your personal workbook (a special file to store macros in that should open hidden but allows using code across files) or as an add-in (similar to personal workbook, but easier to distribute to others and easier to turn off/on and also usually adds to the interface).

Hope this helps

[foxtrotter]

Yeah that helps and as usual raises a few new questions, but I'll leave them for another day. But I did want to ask how you can crack protected worksheets with just a text editor!!!

[Zer0Cool]

I am not sure I can/should share that info on the forums (idk what the rules are about stuff like that). Its pretty easy to find the info online however should you feel the itch to look it up.

The gist of it is I dont crack the password, I simply tell the file there is no password. In this fashion I never know what the original password was but the end result is the worksheet or workbook protection is disabled. Also, since I am not cracking it, the complexity doesnt matter. 4 characters or 40 takes the same amount of time to undo.

Of the 4 types of protection, only file level is "secure" as it encrypts the file. You can still crack the password using software brute force/dictionary attack but complexity matters then. Workbook/Sheet protection can be undone with a text editor, I can undo VBA project passwords with a hex editor in about the same amount of time again regardless of password complexity.

Excel really isnt meant to contain sensitive information. Workbook/Sheet protection are more precautions than protection, good for helping prevent people accidentally overwriting content etc. VBA protection is useless. File level is good until someone can either open to view or open to edit the file, then all bets are off.