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