Read and Write Document Properties of an excel Document – VBA

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 2007Step 2. Roll over Prepare Option available as shown in the below picture:Step 3. Now click on Properties Option visible in Right hand sideStep 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 propertyProp1Name : 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:

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:

A workbook contains a PROTECTED sheet. I wish the user should not know that it is hidden. I could hide it through VBA and password protected the VBA code. However, in the File-Info menu, NAME of the sheet is shown.

Thanks for some other informative blog. The
place else could I get that kind of info written in such
a perfect approach? I have a project that
I am just now running on, and I have been on the glance out for such information.

Hi
I’m looking for a vba code for read/write (i.e. update) some properties of files of a folder not only excel files but all files such as pdf files. (because I created a databank of my pdf files in an excel files!..)
Can you help me?
Thanks

How can I lock the author name on the Properties-Details-Author on excel 2007?? I don’t want to password protect the file. Only want to lock the Author Details. basically don’t want anyone to take the credit for my work.
Please advice.

Hi, I am trying to read the property “Category” in an excel file with two or more set categories. With the methods I found vba only reads the last set category. Do you know how to access all category values