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:
prop1 = ThisWorkbook.BuiltinDocumentProperties(“<Prop1Name>”).value
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:
ThisWorkbook.BuiltinDocumentProperties(“<Prop1Name>”).value = prop1
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: