Removing Document Properties in Excel 2010
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
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
Start Excel 2010.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.
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.
In the Projects pane, click Sheet1.
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
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.
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.