VBA Express : Excel - List all Built In Document Properties in an Excel worksheet

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:

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

Test the code:

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

Sample File:

BuiltInProperties.zip 7.95KB

Approved by mdmackillop

This entry has been viewed 167 times.