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 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
Excel-Document-Properties
Step 4. Now you can see the document property below the Ribbon as shown in the below picture:
Excel-Document-Properties
 
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:
Properties-Updated
 

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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

10 Comments

  1. eXtremeWeb

    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!

    Reply
  2. raj

    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

    Reply
    • saibaba

      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

      Reply
  3. ask

    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.

    Reply
  4. ebrahim

    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

    Reply
  5. LauraAnn

    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.

    Reply
  6. Deep

    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.

    Reply
  7. Dave

    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?

    Reply
    • Vishwamitra Mishra

      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”

      Reply
  8. ChrisK

    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

    Reply

Trackbacks/Pingbacks

  1. 3 Methods to Comment or Uncomment in Excel VBA - Let's excel in Excel - […] Read and Write Document Properties of an excel Document […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest