Using Passwords to Protect Access to Word and Excel Documents

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

One way of controlling access to information in Word and Excel documents is by setting passwords that are required to open or modify the document. The Save As dialog box (File menu) and SaveAs method in Word and Excel provide the option to assign either of two passwords: a password that is required to open a document, and a password that is required to modify the document after it is opened. When you set either open or modify passwords, the password and the contents of the document are encrypted to secure the contents of the document and password. Word and Excel use the RC4 symmetric encryption algorithm to perform this encryption.

Using passwords can have certain drawbacks; for example, users can forget passwords and thus lose access to their documents. For this reason, tools are available for Microsoft Office 2000 that allow administrators to disable the user interface for setting document passwords. For information about how administrators can disable passwords, see the Microsoft Office 2000 Resource Kit (Microsoft Press, 1999).

However, disabling the password user interface doesn't prevent you from using VBA code to set and remove passwords. Nonetheless, before developing a solution that depends on document passwords, you should determine if users or system administrators want to use password protection. If using passwords is not desirable, you can use file-system features to control access to documents. For more information about using file-system access control for Office documents, see "" later in this chapter.

****Note   ****Although the Protect and Unprotect methods in Word and Excel sound similar to the features discussed in this section, they correspond to the Protect Document command (Word) and Protection submenu commands (Excel) on the Tools menu. These methods are used to protect the design of certain elements of a document after it is opened.

To set open or modify passwords in Word or Excel

  1. Open the document or workbook you want to secure.

  2. On the File menu, click Save As.

  3. In the Save As dialog box, click the Commands and Settings button, and then click General Options (Word) or Options (Excel).

  4. Type a password to open or modify the document, and then click OK.

  5. Confirm the password, and then click Save.

Important   Open and modify passwords in Word and Excel are case-sensitive. When you open a protected document, you must use the exact case you used when setting the password. If you lose or forget the password, there is no way to open the document. When password-protecting documents, be sure to write down the password and keep it in a physically secured location.

You can set open and modify passwords in code by using the SaveAs method in Word or Excel. The following example shows how to use the SaveAs method to set open and modify passwords in Word:

Function SetPwd(strNoPwdFile As String, _
                strPwdFile As String, _
                Optional strOpenPwd As String, _
                Optional strModPwd As String) As Boolean
   
   ' This function requires the following arguments:
   ' strNoPwdFile - The path to a document without a password.
   ' strPwdFile - The path and name to save the password-
   '                 protected document.
   '
   ' This function accepts the following optional arguments:
   ' strOpenPwd - A case-sensitive password required to open
   '                 the document.
   ' strModPwd  - A case-sensitive password required to modify
   '                 the document.
   
   On Error GoTo SetPwd_Err
   
   Documents.Open FileName:=strNoPwdFile
   With ActiveDocument
      .SaveAs FileName:=strPwdFile, _
              Password:=strOpenPwd, _
              WritePassword:=strModPwd
      .Close
   End With
   SetPwd = True

SetPwd_End:
   Exit Function

SetPwd_Err:
   MsgBox "Error #: " & Err.Number & vbCrLf _
      & Err.Description
   Resume SetPwd_End
End Function

The SetPwd procedure is available in the modCh17 module in WordPasswords.dot in the ODETools\V9\Samples\OPG\Samples\CH17 subfolder on the Office 2000 Developer CD-ROM.

To use the SetPwd procedure, you must supply the path to a document that has no password, the path and name to save the password-protected document under, and the text of the passwords you want to set.

Once you password-protect a document, if you want to open it with code, you must pass the same case-sensitive password to the PasswordDocument or WritePasswordDocument arguments of the Open method, as shown in the following code fragment:

Documents.Open FileName:=strPwdFile, _
          PasswordDocument:=strOpenPwd, _
          WritePasswordDocument:=strModPwd

This code fragment is from the OpenPwd2 procedure, available in the modCh17 module in WordPasswords.dot in the ODETools\V9\Samples\OPG\Samples\CH17 subfolder on the Office 2000 Developer CD-ROM.

If you hard-code passwords into your code, you should secure access to the code itself in order to preserve the secrecy of a document's password. For information about securing access to code, see "Protecting Your Solution's VBA Code" later in this chapter. If the user of your solution will know the password, you can prompt the user to enter the password in either of two ways. You can display your own dialog box to prompt the user to enter the password, or you can use the Word Password dialog boxes. For information about how to create your own password dialog box, see "Controlling Macro Execution with a Password" later in this chapter.

To use the Word Password dialog boxes, don't pass passwords to the Open method; instead, handle any errors based on a user's response. When a password-protected document is opened, if you don't pass a password to the Open method, Word will automatically display the Password dialog box for each defined password. For an example of how to use the Word Password dialog boxes, see the OpenPwd2 procedure, available in the modCh17 module in WordPasswords.dot in the ODETools\V9\Samples\OPG\Samples\CH17 subfolder on the Office 2000 Developer CD-ROM.

To clear a password from code, open the document by passing the correct passwords, and then use the SaveAs method to set either the Password or WritePassword arguments to a zero-length string (""), as shown in the following code fragment:

With ActiveDocument
   .SaveAs FileName:=strNoPwdFile, _
           Password:="", _
           WritePassword:=""
   .Close
End With

This code fragment is from the ClearPwd procedure, available in the modCh17 module in WordPasswords.dot in the ODETools\V9\Samples\OPG\Samples\CH17 subfolder on the Office 2000 Developer CD-ROM.

You can view complete code samples in WordPasswords.dot in the ODETools\V9\Samples\OPG\Samples\CH17 subfolder on the Office 2000 Developer CD-ROM.

In Excel, you use similar code to set and clear file passwords by using the Password and WriteResPassword arguments of the Open and SaveAs methods of the Workbook object. For code samples, see ExcelPasswords.xls in the ODETools\V9\Samples\OPG\Samples\CH17 subfolder on the Office 2000 Developer CD-ROM.