Macro that revealed some password

[stevez]

By trying to create a macro for doing a save as function I
inadvertently discovered a way to reveal some password.

Here is the macro I fist used:


Sub SaveQuote()
'
' SaveQuote Macro
' Macro recorded 11/10/2005 by MILLER
'

'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sales\Desktop\POW" & Start!F14 &
".xls", FileFormat:=xlNormal, _
Password:=" ", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

I then filled in the values I needed for my quote and ran the macro. I
was given an error message that said I needed an object so I hit debug
and excel showed me this:


Sub SaveQuote()
'
' SaveQuote Macro
' Macro recorded 11/10/2005 by MILLER AUTO CENTER
'

'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sales\Desktop\POW" & Start!F14 &
".xls", FileFormat:=xlNormal, _
Password:="VelvetSweatshop", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

Revealing a password. I tried to unlock the sheet that was protected
and it was not password protected. I next tried to unlock the workbook
and that password didn't work so what did I discover?


Thank You,
Steven Zook
Bored Sales Assistant


BTW If anyone can see what i was trying to do and knows how to do it
lemme know!

 

[Andy]

The file is automatically assigned velvetsweatshop as the password if
the file is protected with no password.

Curious fact: if Workbook password is assigned to Excel document but you
didn't assign password to open (it's empty), password to open is
assigned automatically as VelvetSweatshop. You will not be asked about
this password while you open the file, but your document is
password-protected with this password. That's why you can't assign
VelvetSweatshop to your document as a password to open. If you try to do
this, you will not get an error message, but your document will be
opened without any prompt for a password.

 

[Dave Peterson]

First, you may want to protect your code from being viewed by others.

Inside the VBE
Tools|VBAProject Properties|Protection tab
Check the box for "lock project for viewing"
and assign it a memorable password.

Be aware that this password can be bypassed pretty easily (or even be cracked by
commercial software) and parts of the code can be seen by opening the .xls file
in notepad--so it's not really secure.


Second, excel offers a few different types of protection.

Tools|Protection|Protect sheet
This will keep locked cells on a protected sheet safer. (This protection is
also easily broken.)

Tools|Protection|protect workbook
This is used mostly to protect the structure of the workbook--no
inserting/deleting/moving of sheets will be allowed. You can also protect the
window layout (not often used (well, by me)) to protect how the windows are
arranged for that workbook. (This protection, too, is also easily broken.)

There's the VBAProject protection that is used to protect your code and access
to your project.

And finally(?), there's the file|open protection where you provide a password
(file|SaveAs|tools|General Options|Password to open. Harder to break, but there
is software available commercially to break this.

Each of these is separate and distinct from the others.

========
It looks like you were trying to use a "password to open" password. If your
project were protected, then the code would not have been available to the
casual user. (But even better would be to test and make sure you handle the
unforeseen errors yourself.


There are lots of things that can go wrong. You could try to check each and
issue appropriate warnings. This catches a few (probably not all):

Option Explicit
Sub SaveQuote2()

Dim myFileName As String
Dim myCell As Range

With ThisWorkbook
Set myCell = Nothing
On Error Resume Next
Set myCell = .Worksheets("start").Range("f14")
On Error GoTo 0

If myCell Is Nothing Then
MsgBox "Quote was not saved!" & vbLf & vbLf _
& "The Start Worksheet was deleted." & vbLf _
& "Please contact Steven at xxxx"
Exit Sub
End If

myFileName = myCell.Value
If Trim(myFileName) = "" Then
MsgBox "Please enter something in: " & myCell.Address(0, 0) _
& " of the Start worksheet"
Exit Sub
End If

On Error Resume Next
.SaveAs Filename:="C:\Documents and Settings\sales\Desktop\POW" _
& myFileName & ".xls", FileFormat:=xlNormal, _
Password:="VelvetSweatshop", _
WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
If Err.Number <> 0 Then
MsgBox "Quote was not saved!" & vbLf & vbLf _
& "SaveAs failed." & vbLf _
& "Please contact Steven at xxxx"
Err.Clear
End If
End With

End Sub

One thing that could make your coding life worse is if you have to insert a new
row/column so the address of the cell that is going to hold the filename
changes. You may want to considering giving those important cells that are used
in your code names (insert|name|define). Then you can use that name in your
code.

Inserting/deleting rows or columns won't affect that name (well, if you don't
delete that cell!).


Then this line
Set myCell = .Worksheets("start").Range("f14")
would become:
Set myCell = .Worksheets("start").Range("InputFileName")

Remember to protect the VBAProject to stop most from viewing your password.

 

[Dave Peterson]

I think your curious fact is just that excel supports multiple areas of
protection.

You can protect the worksheet (tools|Protection|protect sheet)
You can protect the workbook (Tools|protection|protect workbook)
You can protect the workbook from being opened:
(File|saveas|general options|Password to open)
(as well as the Project (in the VBE).)