protected Excel spreadsheet

Discussion in 'Mac Apps and Mac App Store' started by Stevp1, Dec 20, 2005.

  1. Stevp1 macrumors regular

    Joined:
    Dec 8, 2003
    #1
    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.
     
  2. stevep macrumors 6502a

    stevep

    Joined:
    Oct 13, 2004
    Location:
    UK
    #2
    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').
     
  3. Stevp1 thread starter macrumors regular

    Joined:
    Dec 8, 2003
    #3
    I'll check that out.

    nice screen name, by the way...:D
     
  4. mikeyredk macrumors 65816

    Joined:
    Mar 13, 2003
    #4
    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
     
  5. Stevp1 thread starter macrumors regular

    Joined:
    Dec 8, 2003
    #5
    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?
     
  6. stevep macrumors 6502a

    stevep

    Joined:
    Oct 13, 2004
    Location:
    UK
    #6
    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.
     
  7. Stevp1 thread starter macrumors regular

    Joined:
    Dec 8, 2003
    #7
    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!
     
  8. Kernow macrumors 65816

    Kernow

    Joined:
    Sep 30, 2005
    Location:
    Kingston-Upon-Thames
    #8
    Confused the hell out me for a moment :eek: . I thought you were having a conversation with yourself!
     
  9. jeremy.king macrumors 603

    jeremy.king

    Joined:
    Jul 23, 2002
    Location:
    Fuquay Varina, NC
    #9
    Couldn't you just ask the customer to send an unlocked version?
     
  10. Stevp1 thread starter macrumors regular

    Joined:
    Dec 8, 2003
    #10
    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.
     

Share This Page