In this Article we will learn how to get document property of an excel workbook using Excel VBA. Before I jump in to the VBA code let’s have a look Where to see document properties in Excel Document?

How to see document property of an Excel document

Follow the below steps to see the properties for the document:

Step 1. Click on Office Button in Excel 2007
Step 2. Roll over Prepare Option available as shown in the below picture:
Step 3. Now click on Properties Option visible in Right hand side

Step 4. Now you can see the document property below the Ribbon as shown in the below picture:

 
Here you can read the document property which is already updated. You can write the property here and save it. It means you can read and write a document property from the above screen.

Now you know how to read and write the properties of an excel document. Now I will show you how to do the same activity using VBA code. Using Workbook.BuiltinDocumentProperties property user can read and write the document property of an excel workbook.

What is Workbook.BuiltinDocumentProperties ?

This is a Microsoft Excel Workbook property which allows users to read and write the document property. This returns a document properties object which has a collection of all the properties of the document.

1. How to Read Document Property using Workbook.BuiltinDocumentProperties

Use the below vba syntax to get the document property of the excel document:

Syntax

prop1 = ThisWorkbook.BuiltinDocumentProperties(“<Prop1Name>”).value

Where:

Prop1 : is a variable where you want to store the value of a property
Prop1Name : This is the Name of the property by which it is referred in the returned collection of properties

Example 1: Get the Author Name from the document property

Name of the item where Author Name property value is stored is author. Therefore to get the author name from the document property vba code will look like this:

Function Get_Author_Name()
    Dim AuthorName As String
    AuthorName = ThisWorkbook.BuiltinDocumentProperties("author").Value
End Function

Example 2: Few more frequently used properties

Function Get_Document_Properties()
    Dim LastAuthorName
    Dim CreatedOn
    Dim LastSavedOn
    Dim Title
    Dim Comments
    'To retrieve the last Author of the File
    LastAuthorName = ThisWorkbook.BuiltinDocumentProperties("last author").Value
    'To get the created on date and time
    CreatedOn = ThisWorkbook.BuiltinDocumentProperties("creation date").Value
    'to get the date and time when last time document was saved
    LastSavedOn = ThisWorkbook.BuiltinDocumentProperties("last save time").Value
    'to get the title of document updated in document property
    Title = ThisWorkbook.BuiltinDocumentProperties("title").Value
    'to get the Author's comment added in document proprty
    Comments = ThisWorkbook.BuiltinDocumentProperties("title").Value
End Function

Example 3: VBA code to display all the Properties Name its values

If you want to display all the properties of an Excel Workbook you can use For Loop to traverse all the items of the BuiltinDocumentProperties collection. Below is the VBA code which will list all the proprties Name and corresponding values in your excel sheet.

Function list_All_Properties()
Dim iRow As Integer
iRow = 1
On Error Resume Next
'below loop will traverse for all items of this collection of properties
For Each prop In ThisWorkbook.BuiltinDocumentProperties
    Range("A" & i).Value = prop.Name 'Property Name like "Author" etc
    Range("B" & i).Value = prop.Value 'Property value like Author name updated in proprty
    iRow = iRow + 1
Next
End Function

2. How to Write Document Property using Workbook.BuiltinDocumentProperties

This is very simple. All you need to do is reverse the operation. Now you need to assign a value to the property. Hence the VBA code synatx will look like this:

Syntax

ThisWorkbook.BuiltinDocumentProperties(“<Prop1Name>”).value = prop1

Where:

Prop1 : is the value of a property (Like Author Name = Vishwa)
Prop1Name : This is the Name of the property which you want to update with the above value

Example 1: Set the Author Name in document property using VBA

Name of the item where Author Name property value is stored is author. Therefore to update the author name in the document property vba code will look like this:

Function Update_Author_Name()
    Dim AuthorName As String
    AuthorName = "Vishwa"
    ThisWorkbook.BuiltinDocumentProperties("author").Value = AuthorName
End Function

Example 2: Few more frequently used properties

Function Update_Document_Properties()
    Dim Subject
    Dim Status
    Dim Category
    Dim Title
    Dim Comments
    'Assign property value to the variables
    Subject = "Subject of the Document"
    Title = "My Title"
    Comments = "Author's comment"
    Status = "Complete"
    Category = "Finance"
    'To update the title of document in document property
    ThisWorkbook.BuiltinDocumentProperties("title").Value = Title
    'to update the Author's comment in document proprty
    ThisWorkbook.BuiltinDocumentProperties("comments").Value = Comments
    'to update Subject in document proprty
    ThisWorkbook.BuiltinDocumentProperties("subject").Value = Subject
    'to update the status in document proprty
    ThisWorkbook.BuiltinDocumentProperties("content status").Value = Status
    'to update the Category in document proprty
    ThisWorkbook.BuiltinDocumentProperties("category").Value = Category
End Function

After running the above code all the values will be set to the corresponding property field in document property. refer the below picture:

 

If you have any doubt or suggestion do let me know through comment or on my facebook fan page