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:
He Vishwamitra,
Could you also explain how I can get the info about who is the active user into a userform? I would like to call the information into a textbox or something.
Thanks!
Dear Mr Vishwa,
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.
How to remove that Display in the File-Info menu.
I am using 2007.
Thanks and Regards
Hi Raj,
File vba and sheet also preotected, Right.
Then why you again a extra sheet for data table / details.
Combine all details, which is in protected and hidden sheet to the visible sheet which editable to others should lock and give only permitted cells under unlocked mode and protected the visible sheet.
By this User only knows about a visible sheet but he doesn’t know the protected and hidden details of the same sheet.
If he opened File-menu he only thinks that only one sheet in workbook.
Alternatively, Use office 2010 or 2013 to avoid your doubt / confusion.
– Saibaba
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
I am trying to add text to the Tags field, which is visible on the file save as box.
Do you have any advice?
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.
im looking to write the “last author” property to Anonymous to protect user anonymity.
Application.Workbooks(WBname).BuiltinDocumentProperties(“Last Author”) = “Anonymous”
throws an out of script range error.
Any help?
Hi, Where is your workbook? You should use this by using ThisWorkbook keyword to set the values within the same workbook.
ThisWorkbook.BuiltinDocumentProperties(“last author”).Value = “anonymous”
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