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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.