Nilesh’s Weblog

August 29, 2008

Excel WorkSheet Password

Filed under: Microsoft Excel — Tags: , , , , — deshnilesh @ 5:12 pm

I again forgot my password for a excel worksheet and then begin mission to crack it :). Fortunately I cam across this code which will give you a alternate password. There is more detail discussion about how it works on the site, I am copying the code here for my reference so in future if I need it again I won’t have to search again.

Sub PasswordBreaker()
‘by Bob McCormick on NG: microsoft.public.excel.misc

‘on 22 May 2001.
‘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 126ActiveSheet.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

Advertisements

August 18, 2008

Hyperlinks in Excel

Filed under: Microsoft Excel — Tags: , — deshnilesh @ 5:12 pm

Well here’s one more excel trick which I learned today. I had a file which was having hyperlinks to all of its cells and I wanted to get rid of it. Here’s the code which will do it for you in seconds!! –

Sub RemoveAllHyperlinks()
‘Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub

If you want to remove Hyperlinks from all the sheets then just add a loop to browse thru all sheets and then remove the hyperlinks or write a procedure to browse thru the sheets and then call above procedure.

Sub RemoveAllHyperlinksFromAllSheets()
‘Remove all hyperlinks from all sheet
Dim varSheet As Worksheet
For Each varSheet In ActiveWorkbook.Worksheets
varSheet.Hyperlinks.Delete
Next
End Sub

What if you want to remove hyperlinks for a particular range? That is also not a problem just select the cells and run the code –

Sub RemoveHyperlinksForSelectedCells()
‘Remove all hyperlinks from selected cells
For Each cell In Selection
cell.Hyperlinks.Delete
Next
End Sub

Blog at WordPress.com.