ADS

Featured

Unprotect locked Excel spreadsheets using macro

Today in my service, I had to unlock an excel spreadsheet to edit some fields that those who protected, did not develop correctly, and were displaying incorrect values, so I ran across the web to look for something, and found a method by creating a macro.

The spreadsheet was in Office 2007 format, but it is possible to save for Office 2003 in compatibility mode. The computer I used to try to do the trick was Windows XP with Office 2003.


Due to a very simple security flaw, it is possible to add any macro to any type of spreadsheet in Office 2003, including protected ones. With a macro, from which you can insert it into several protected worksheets, you can quickly unlock it. For this, you must keep on the protected sheet, go to the Tools> Macro> Macros ... menu or simply press ALT + F8.

The macro creates a loop using all possible passwords, as excel has no limit on attempts, and attempts on a local computer are extremely fast than on internet services, all possible passwords are tested and the spreadsheet is unlocked, removing the password.

For those who do not know, the feature of protecting spreadsheet, serves to keep the data as "read only", fields, functions, among others, preventing modification without the correct password.




Being protected, the person who wants to change any protected field, an alert message will appear as below:

Step by step:
Create a new macro with the name: UnprotectPlanilhaAtiva.

Soon after, an editable window will appear with two lines filled in as follows:



Between the two lines, insert the code:

Dim i,i1,i2,i3,i4,i5,i6,j,k,l,m,n: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 "Planilha desprotegida com sucesso!!!": Exit Sub
End If: Next: Next: Next: Next: Next: Next: Next: Next: Next: Next: Next: Next

Close the Macro editing screen and return to your excel spreadsheet. Press ALT + F8, select the "UnprotectPlanilhaAtiva" macro and click "Run".



The macro remains inside the protected worksheet, click on the desired worksheet, and using the ALT + F8 command, execute the macro. Wait until the confirmation window from which you were unprotected appears.



The bigger the password that was created, the longer it takes for the code to be executed, that is, do not worry if it happens "Not responding", because in reality, the code that is being executed, usually consumes processor resources , and Windows identifies that this generated an infinite loop, but it is not infinite, and does not last more than 30 seconds with a 17-digit password (tested).

7 comments:

  1. Aristote,bom dia !!!
    Caro desculpe pela sinceridade e objetivo, estou começando duas semanas em uma empresa e estou tendo muito problema com uma planilha de controle de estoque que está em vba excel protegida e não sei a senha e a pessoa que criou a mesma saiu e não deixou o problema é que ele configurou a mesma para determinadas linhas e quando tento cadastro um novo produto na aba(demonstrativo) some um item tem como de ajuda por favor.
    João e-mail; [email protected]

    ReplyDelete
  2. Ai amigo deu super certo.
    Não teria um código desse tipo só
    que para desbloquear um arquivo excel vba?
    Pois o caso esta tão critico que eu não
    consigo sequer grava a macro, logo não da
    para executa-lá. É como se a restrição estive se
    protegendo ate os UserForms e não só as planilha. meu e-mail é [email protected]

    ReplyDelete
  3. não deu certo, deu um erro de Compilação: End if sem bloco if

    ReplyDelete
  4. Estava copiando e colando e não dava certo, fui ate outra pagina e copiei o arquivo, agora sim deu certo, valew:

    Sub DesprotegerPlanilhaAtiva()
    Dim i, i1, i2, i3, i4, i5, i6 As Integer, j As Integer, k As Integer, l As Integer , m As Integer, n 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 "Planilha desprotegida com sucesso!!!"
    Exit Sub
    End If
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    End Sub

    ReplyDelete