Removing Document Properties in Excel 2010

  • Article

Office Quick Note banner

Handy Programming Tips for Microsoft Excel 2010: Learn how to remove sensitive information from a Microsoft Excel 2010 workbook.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

As you create and use a workbook, personal, potentially sensitive, and hidden information might be added, frequently without your knowledge. One way to remove this information is by using the Prepare for Sharing option that appears when you click Info on the File tab. You can also use the RemoveDocumentInformation method in code. In this topic, you programmatically add information to the workbook properties and then programmatically remove that information. To complete this task, you must do the following:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

In this task, you add programming code that adds various properties values to a workbook and then removes that information by using the RemoveDocumentInformation method.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub TestRemoveDocumentInformation()
  ' Set up a named range with a comment:
  Dim nm As Name
  Set nm = Names.Add(Name:="TestNamedRange", _
   RefersToR1C1:="=Sheet1!R1C1:R7C3")
  nm.Name = "NamedRange"

   ' To see the comment, click the Formulas tab menu and then 
   ' click Name Manager.
  nm.Comment = "Here is a comment"

  ' Set some document properties:
  Dim props As Office.DocumentProperties
  Set props = ActiveWorkbook.BuiltinDocumentProperties
  props("Author").Value = "Author Name"
  props("Subject").Value = "Test Document"

  ' Add a comment, which includes your name.
  ' Removing information converts author name to Author

  Dim cmt As Comment
  Set cmt = Range("B3").AddComment
  cmt.Visible = False
  cmt.Text "This is a test"

  ' Remove comments, defined name comments, personal information, and document properties.
  ActiveWorkbook.RemoveDocumentInformation xlRDIComments
  ActiveWorkbook.RemoveDocumentInformation xlRDIDefinedNameComments
  ActiveWorkbook.RemoveDocumentInformation xlRDIRemovePersonalInformation
  ActiveWorkbook.RemoveDocumentInformation xlRDIDocumentProperties
End Sub

Test the Solution

In this task, you step through the VBA code that adds property information to a workbook and then removes that information.

To run the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Press F8 to step through the code line-by-line and watch the code interact with Excel. As you step through the code, verify that the code adds the property values as described in the code comments.

Next Steps