Excel

List all Built In Document Properties in an Excel worksheet

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

Ken Puls

Description:

This macro will list all "BuiltIn" Document Properties in the current file, as filled in on the "File|Properties" tab. 

Discussion:

You may want to track and compare information about the workbook as compared to the last time it was saved. This routine will provide information that Excel tracks on a regular basis. 

Code:

instructions for use

			

Option Explicit Sub ListProperties() 'Macro Purpose: To list all BuiltIn Document Properties 'for the workbook in an Excel worksheet Dim wb As Workbook, l As Long Set wb = ActiveWorkbook 'On Error to avoid items without Value properties On Error Resume Next With wb 'Set up header info With .Sheets(1) .Range("A1").Value = "Property" .Range("B1").Value = "Value" End With 'List Document Property Name and Value For l = 1 To .BuiltinDocumentProperties.Count .Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).Value = _ .BuiltinDocumentProperties.Item(l).Name .Sheets(1).Range("A65536").End(xlUp).Offset(0, 1).Value = _ .BuiltinDocumentProperties.Item(l).Value Next l End With On Error Goto 0 'Clean up variables Set wb = Nothing End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. From Excel, press Alt+F8 to show the Macro dialog box.
  2. Choose "ListProperties" and click "Run."
  3. You should now see the BuiltInDocument properties.
  4. Save the file and try it again. (Data will append to bottom.)
  5. You should notice that the "Last Save Time" has changed.
 

Sample File:

BuiltInProperties.zip 7.95KB 

Approved by mdmackillop


This entry has been viewed 148 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express