【技巧應用】Excel破解工作表保護密碼

使用excel過程中，出於對數據的保護，很多時候都會對工作表進行加密處理，保護後無法編輯。

但是帶來安全的同時也帶了另一個麻煩事，那就是密碼遺忘，不懂編程也沒有關係，破解其實很簡單，一種VBA破解法（代碼是現成的），另一種是壓縮包破解法，本篇就來和大家分享一下如何對excel中的工作表保護密碼進行破解。

方法一：代碼破解法

其實工作表的保護只能是常規的保護，對於Excel接觸較深的人群形同裸奔，百度上一大把保護密碼破解的代碼，都是現成的，下面是操作方法。

點擊【開發工具】，點擊【VBA編輯器】

點擊右鍵，插入【模塊】

將代碼粘貼進去點擊執行，彈出的提示全部點確定即可（代碼在下方）

代碼如下（複製即可）：

Public Sub AllInternalPasswords()Const DBLSPACE As String = vbNewLine & vbNewLineConst AUTHORS As String = DBLSPACE & vbNewLine & _"Adapted from Bob McCormick base code by" & _"Norman Harker and JE McGimpsey"Const HEADER As String = "AllInternalPasswords User Message"Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"Const REPBACK As String = DBLSPACE & "Please report failure " & _"to the microsoft.public.excel.programming newsgroup."Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _"now be free of all password protection, so make sure you:" & _DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _DBLSPACE & "Also, remember that the password was " & _"put there for a reason. Don't stuff up crucial formulas " & _"or data." & DBLSPACE & "Access and use of some data " & _"may be an offense. If in doubt, don't."Const MSGNOPWORDS1 As String = "There were no passwords on " & _"sheets, or workbook structure or windows." & AUTHORS & VERSIONConst MSGNOPWORDS2 As String = "There was no protection to " & _"workbook structure or windows." & DBLSPACE & _"Proceeding to unprotect sheets." & AUTHORS & VERSIONConst MSGTAKETIME As String = "After pressing OK button this " & _"will take some time." & DBLSPACE & "Amount of time " & _"depends on how many different passwords, the " & _"passwords, and your computer's specification." & DBLSPACE & _"Just be patient! Make me a coffee!" & AUTHORS & VERSIONConst MSGPWORDFOUND1 As String = "You had a Worksheet " & _"Structure or Windows Password set." & DBLSPACE & _"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _"Note it down for potential future use in other workbooks by " & _"the same person who set this password." & DBLSPACE & _"Now to check and clear other passwords." & AUTHORS & VERSIONConst MSGPWORDFOUND2 As String = "You had a Worksheet " & _"password set." & DBLSPACE & "The password found was: " & _DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _"future use in other workbooks by same person who " & _"set this password." & DBLSPACE & "Now to check and clear " & _"other passwords." & AUTHORS & VERSIONConst MSGONLYONE As String = "Only structure / windows " & _"protected with the password that was just found." & _ALLCLEAR & AUTHORS & VERSION & REPBACKDim w1 As Worksheet, w2 As WorksheetDim i As Integer, j As Integer, k As Integer, l As IntegerDim m As Integer, n As Integer, i1 As Integer, i2 As IntegerDim i3 As Integer, i4 As Integer, i5 As Integer, i6 As IntegerDim PWord1 As StringDim ShTag As Boolean, WinTag As BooleanApplication.ScreenUpdating = FalseWith ActiveWorkbookWinTag = .ProtectStructure Or .ProtectWindowsEnd WithShTag = FalseFor Each w1 In WorksheetsShTag = ShTag Or w1.ProtectContentsNext w1If Not ShTag And Not WinTag ThenMsgBox MSGNOPWORDS1, vbInformation, HEADERExit SubEnd IfMsgBox MSGTAKETIME, vbInformation, HEADERIf Not WinTag ThenMsgBox MSGNOPWORDS2, vbInformation, HEADERElseOn Error Resume NextDoFor i = 65 To 66: For j = 65 To 66: For k = 65 To 66For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126With ActiveWorkbook.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 .ProtectStructure = False And _.ProtectWindows = False ThenPWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)MsgBox Application.Substitute(MSGPWORDFOUND1, _"$$", PWord1), vbInformation, HEADERExit DoEnd IfEnd WithNext: Next: Next: Next: Next: NextNext: Next: Next: Next: Next: NextLoop Until TrueOn Error GoTo 0End IfIf WinTag And Not ShTag ThenMsgBox MSGONLYONE, vbInformation, HEADERExit SubEnd IfOn Error Resume NextFor Each w1 In Worksheetsw1.Unprotect PWord1Next w1On Error GoTo 0ShTag = FalseFor Each w1 In WorksheetsShTag = ShTag Or w1.ProtectContentsNext w1If ShTag ThenFor Each w1 In WorksheetsWith w1If .ProtectContents ThenOn Error Resume NextDoFor i = 65 To 66: For j = 65 To 66: For k = 65 To 66For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126.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 Not .ProtectContents ThenPWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)MsgBox Application.Substitute(MSGPWORDFOUND2, _"$$", PWord1), vbInformation, HEADERFor Each w2 In Worksheetsw2.Unprotect PWord1Next w2Exit DoEnd IfNext: Next: Next: Next: Next: NextNext: Next: Next: Next: Next: NextLoop Until TrueOn Error GoTo 0End IfEnd WithNext w1End IfMsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADEREnd Sub

此時工作表保護密碼已清除，直接保存即可，如果是XLSX格式的工作薄，保存退出時會有提示，也就是非宏的工作薄無法保存宏代碼。
直接點是即可

方法二：壓縮包破解法

將文件後綴名直接改為RAR，如果看不到後綴名，說明未將後綴名選項打勾，如果是WIN7系統，可在文件夾選項中設置。
將【隱藏已知文件類型的擴展名】選項的勾去掉即可。
下面更改後綴名
雙擊打開壓縮包，找到名為XL文件夾並點擊進去。
找到WORKSHEETS點擊進去
將sheet1拖出來（sheet1為保護後的工作表），拖出來後不要關閉壓縮包，稍後更改完此文件後還需要拖回來替換掉原來的sheet1文件。
更改後綴名為txt文檔（稍後編輯完後還需要更改回xml格式）
雙擊打開後一堆亂七八糟的東西，不用記住那麼多，只用查找prot所在的尖角括號<> 的內容，刪除即可。
保存，並更改回xml格式。
將此文件拖回來替換掉原來的sheet1文件。
關閉壓縮包，將壓縮包後綴名更改回XLSX格式，雙擊打開。
此時就可以編輯了。
結語：工作表保護密碼破解簡單，但其實很多操作同樣也很簡單，就看你是否知道這些技巧，很多時候以為自己走了捷徑，但殊不知還有比你更快無數倍的方法很輕鬆的解決問題，這就是技巧的優勢，學表不能總盯著一個知識點學到大師級別，比如一個簡單的合併單元格功能花無數的時間去扒核心的原理和無數的實例，其實完全沒必要，因為你只需要懂它怎麼使用，能幹什麼即可，真要把合併單元格學到大師級別，那也是等學到函數數組級別，將自己的工作優化，節省大量的時間之後才回過頭做這些事情。
◆學而不用則殆，希望大家學習後利用到自己的工作中，哪怕是點滴的應用，也是一種進步。給自己的工作帶來非常大的方便。