StevenWhiting.com

A place for info I've learnt in IT & stuff. (I get a little kick back from affiliate ads & links, just so you are aware)

Browsing Posts tagged Excel

To be clear, I only copy and paste other peoples sites sometimes in case the page I’ve linked to fails, which has happened a few times when people don’t carrying on hosting their sites.

 

http://wheatblog.com/2011/08/where-is-the-excel-personal-macro-workbook-located/

Sometimes I blog things mostly so I can remember them and in the off chance that they might be useful to others. This post falls into that category. If you use Microsoft Excel, and you have a macro that you want to be available globally–in any open workbook–you can place it in your Personal Macro Workbook, which is just an Excel Workbook (in binary, XLSB, format, for speed) that lives at a particular location, where Excel will look for it whenever it launches.

On Windows 7, it lives here: 1

C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

On Windows XP, it lives here:

C:\Documents and Settings\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB

In either case, the easy way to create PERSONAL.XLSB is not to muck around with the file system directly. Instead, just record a macro in Excel and, when you’re prompted to save it, choose Personal Macro Workbook from the Store Macro In drop-down menu on the Record Macro dialog box. Record yourself typing a few numbers and adding some formatting to them or something similar. Afterwards, you can open up the Personal Macro Workbook via the Visual Basic button on the Developer tab later and delete whatever you record. Once you create it, your Personal Macro Workbook will be listed in the VBA Editor as “VBAProject (PERSONAL.XLSB)”.

Some Additional Details for the Truly Nerdy

The location of the Personal Macro Workbook is a little confusing, because older versions of Excel stored it in an XLSTART folder buried inside the Program Files folder. Even though you can’t write any files to it, Excel 2007 and Excel 2010 also have XLSTART folders inside the Program Files directory. Here are the paths:

For Excel 2010:

C:\Program Files\Microsoft Office\Office14\XLSTART\

For Excel 2007:

C:\Program Files\Microsoft Office\Office12\XLSTART\

“Office 14” is the internal name for Office 2010. It’s actually the 13th version of Office, but Microsoft saw fit to skip naming it Office 13, due, one suspects to superstition. So “Office 12” is Office 2007, “Office 11” is Office 2003, and so on. If you happen to be stuck running Excel 2003 on a Windows XP box, you’ll find PERSONAL.XLSB at this path:

C:\Program Files\Microsoft Office\Office11\XLSTART\

.

http://youtu.be/C9Lvf44qcvw

http://mcgimpsey.com/excel/removepwords.html

Only got it to work in Excel 2003.  Running the allinteralpasswords.xls with the protected excel file open and then going to tools, macro, macro and then should see the allinteralpasswords there and click run.

Worked.

Removing Internal XL passwords

Note: For a discussion of File or VBA Project password protection, see here.

Internal XL passwords are about as useful for security as tissue paper. The reason is that the passwords you enter (i.e., with Tools/Protect/Protect Worksheet or /Protect Workbook) are not used directly in protection. Instead they are hashed (mathematically transformed) into a much less secure code. Effectively, any password of any length is transformed into a string of 12 characters, the first 11 of which have one of only two possible values. The remaining character can have up to 95 possible values, leading to only

2^11 * 95 = 194,560

potential passwords. This may seem like a lot, but it only takes a few seconds for a modern computer to try them all. As a comparison, a 4-character password containing just the 26 lower case alphabet characters has 456,976 combinations, and a 3-character password consisting of lower case, upper case and the digits 0-9 will have 238,328 combinations.

Again, it doesn’t matter what your original password is, one of those 194K strings will unlock your sheet or workbook.
Bypassing Protection

In many versions of XL, bypassing protection is as easy as selecting the entire worksheet, copying it and pasting it to a new worksheet. If you have a lot of links between sheets, it can be difficult to substitute the new sheet, but you can see what’s going on at least.
Add-ins and macros

There have been macros published (mostly derived from one that I first saw published by Bob McCormick) for years in the newsgroups. There are some add-ins that will remove passwords – search the Google newsgroup archives to find them. Einar Ståle Huse has written one of the most popular add-ins – password.xla.
The AllInternalPasswords Macro

This macro, for which the true origin is lost in antiquity, will unlock all the internal passwords in your workbook. It will report which password strings worked (so that if you have other workbooks by the same author, you can try it on them), but it will NOT report the original password (it can’t – it’s never stored – only the hash is saved in the file). I first saw it in a post by Bob McCormick. Norman Harker did an outstanding job in modifying it for workbook as well as worksheet protection. I mostly cleaned it up and made it a bit more efficient.

It shouldn’t take more than a few seconds (certainly less than a minute) to find each password if you have a relatively up to date computer.

Working passwords will be returned in the form

AAABABBABABX

where the first 11 characters will be As or Bs and the X represents a character from ASCII 32 (” ” or space) to ASCII 126 (“~”, or tilde). While the sheet or workbook will automatically be unprotected, the macro suggests you write this code down, not only for future use, but, people being creatures of habit, it may work on another sheet/workbook by the same author. Again, since it’s not returning the original password, it’s unlikely to be useful in breaking file or VBA project passwords.

You can download the workbook allinternalpasswords.xls if you don’t want to cut and paste the macro below. The workbook is hidden, and has an attached toolbar with a button to start the macro. Activate the workbook you want to unlock and click the button. The code is unlocked so you may examine and modify it as needed.

DISCLAIMER: Please note that breaking password protection MAY violate laws or regulations in your jurisdiction. In the United States (currently) it is certainly OK to break password protection on sheets that you developed for your personal use, or for which you have permission from the author or owner. It’s probably OK if you own the workbook, even if it was written by someone else. But neither Norman nor I are intellectual property lawyers in ANY jurisdiction, so, if in doubt, ask the original author, and if you can’t ask – don’t use it!

Public Sub AllInternalPasswords()
‘ Breaks worksheet and workbook structure passwords. Bob McCormick
‘ probably originator of base code algorithm modified for coverage
‘ of workbook structure / windows passwords and for multiple passwords

‘ Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1)
‘ Modified 2003-Apr-04 by JEM: All msgs to constants, and
‘ eliminate one Exit Sub (Version 1.1.1)
‘ Reveals hashed passwords NOT original passwords
Const DBLSPACE As String = vbNewLine & vbNewLine
Const 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 & VERSION
Const MSGNOPWORDS2 As String = “There was no protection to ” & _
“workbook structure or windows.” & DBLSPACE & _
“Proceeding to unprotect sheets.” & AUTHORS & VERSION
Const 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 & VERSION
Const 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 & VERSION
Const 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 & VERSION
Const MSGONLYONE As String = “Only structure / windows ” & _
“protected with the password that was just found.” & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean

Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do ‘dummy do loop
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
With 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 Then
PWord1 = 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, HEADER
Exit Do ‘Bypass all for…nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
‘Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
‘Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do ‘Dummy do loop
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
.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 Then
PWord1 = 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, HEADER
‘leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do ‘Bypass all for…nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub