Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

protected Excel spreadsheet

S

Stevp1

macrumors regular
Original poster
Dec 8, 2003
171
11
I received an excel sheet from one of our customers. It's protected, but as a vendor, we're supposed to be able to access a hidden section to enter our products.

When I open the sheet on any PC, I get a warning that says "you are about to access confidential information, do you agree?" or something like that, and I get to the hidden part.

When I open it on my mac, I'm asked to enable macros, but that agreement page doesn't apppear, and I can't view the info.

Is protection handled differently on the Mac? This is Excel 2004 by the way.
 
Stevp1 said:
When I open the sheet on any PC, I get a warning that says "you are about to access confidential information, do you agree?" or something like that, and I get to the hidden part.
Click to expand...
This bit sounds as though its a macro written by the originator of the spreadsheet. It also sounds as though the security settings of Excel on the pc are set to open spreadsheets with macros automatically.
If you open the spreadsheet on the Mac, allow the macros when asked by the dialogue. You then need to run the macro which asks the question in the quote above. Usually a macro is assigned to a shortcut key or a button on the spreadsheet drawn by the originator. Go to Tools > Macro > Macros to see whats there, and to see how to run the macro which allows you to view the hidden sheet.
Protection is different on the Mac - there doesn't seem to be the facility to change the settings on running macros as there is on the pc (where one of the menu items allows you to change between 'run automatically', 'ask every time' and 'never run at all').
 
Or you can unlock it

Code: 
Sub PasswordBreaker()
    'Author unknown
    'Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
        Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
        Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    If ActiveSheet.ProtectContents = False Then
        MsgBox "One usable password is " & Chr(i) & Chr(j) & _
            Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
            Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
         Exit Sub
    End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
End Sub

Stick that in as VBA and you can crack it
 
Sweet. It's a last resort, but it's nice to have it.

SteveP, I was able to find the macro and launch it, but it looks like it's referencing another part of the file somewhere. I'm know there's another worksheet somewhere hidden that contains all our vendor info. Could it be the macros have to be launched in a certain order?
 
There may well be more than one macro - its likely that the value returned by the dialog in the 'Do you want to..." macro is used to determine whether to run an 'OK here ya go...' macro which allows you full access or a 'You can't go any further' macro only giving you restricted access.
Go to Tools > Macro > Macros menu will give you a list of all macros available, and you may be able to deduce from the names assigned which one does what. You should also be able to open them for editing - the VBA code is fairly self-explanatory and you need to look for the names of worksheets, comparing them to the visible ones in your workbook.
The menu command Format > Sheet > Hide (or Unhide) may also appear somewhere in the code, or you can use this command yourself if you have found the worksheet name.
Of ourse, it may be that the spreadsheet you have isn't complete - is it an exact copy of the original or has it been doctored?
ps I tried the password cracking code and it didn't work for me.
Edit - It did, but its a bit strange - it works out the password, unprotects the sheet and then displays a completely non-sensical password that you don't need to use as its already unprotected the sheet.
 
Thanks Steve.

yes, there are a bunch of macros in the list. I'll try the VBA code next. Don't know why I didn't think of that before.

Thanks, I'll be back!
 
kingjr3 said:
Couldn't you just ask the customer to send an unlocked version?
Click to expand...

Unfortunately no. It's a large national corporation. I suppose we could ask if they have a Mac friendly version, but I suspect I know what the answer would be.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back