I have a .NET winforms app that automates Excel and checks for a worksheet password. The requirements are to be able to detect
1) that the protection is turned off
2) that the password is removed (protected but there is no password)
3) that the password matches the correct password from a database
To meet the second requirement the program calls the Worksheet.Unprotect command with a null string, capturing the error. If error as expected, the 3rd check is made. If no error, then the Unprotect worked without a password ==> password was removed.
The code sample below has these checks.
The application can do this fine with Office 2003. I have since had my dev machine updated to Office 2007 and it no longer works as it did. When I call the Worksheet.Unprotect, Excel prompts for the password!
I need to know how this should be accomplished in the new version of Excel or if there is a way to reference the old PIA. No matter what if I set a reference to Excel 11 it is replaced with the PIA for 12 in the GAC.
'return true if unprotect of worksheet does not generate an error
'all other errors will bubble up
'return false if specific error is "Password is invalid..."
Try
'detect unprotected or no password
If oWorksheet.ProtectContents Then
'try with no passsword and expect an error
'if no error then raise exception
Dim blnRaiseException As Boolean = True
Try
'oWorksheet.Unprotect(vbNullString)
oWorksheet.Unprotect()
Catch ex As Exception
blnRaiseException = False
End Try
If blnRaiseException Then
Throw New ExcelSheetNoPasswordException
End If
oWorksheet.Unprotect(strPwd)
'no error so if we get here -- success
fnCheckWorksheetPwd = True
'leave as it was -- this may still cause workbook to think it is changed
oWorksheet.Protect(strPwd)
Else
Throw New ExcelSheetNotProtectedException
End If
Catch COMex As System.Runtime.InteropServices.COMException
'handle error code -2146827284
If COMex.ErrorCode = -2146827284 Then
'this is the error we're looking for
Else
Throw
End If
Catch ex As Exception
Throw
End Try