Export (0) Print
Expand All

Removing Document Properties in Excel 2010

Office 2010

Office Quick Note banner

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

Last modified: May 05, 2011

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:

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 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
    
    

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.

Show:
© 2015 Microsoft