[Excel VBA] How to Read and Write Workbook Properties

Excel files store additional information (metadata) such as the author, title, subject, and creation date in their “Properties.” You can usually check this information via the File > Info screen. However, by using VBA, you can automatically read or rewrite this information programmatically.

In this article, I will explain how to manipulate the built-in document properties of a workbook using VBA, covering both “Reading” and “Writing.”

Reading Workbook Properties

First, let’s look at how to retrieve existing property information stored in a workbook. This is useful when you want to record the creation date or the last modified by user as a log.

Completed VBA Code

Sub ReadDocumentProperties()

    ' Declare variables
    Dim targetBook As Workbook
    Dim message As String
    
    ' Target the workbook where this macro is written
    Set targetBook = ThisWorkbook
    
    ' Work with BuiltinDocumentProperties using a With block
    With targetBook.BuiltinDocumentProperties
        ' Read the value of each property
        message = "--- Workbook Properties ---" & vbCrLf & vbCrLf & _
                  "Title: " & .Item("Title").Value & vbCrLf & _
                  "Subject: " & .Item("Subject").Value & vbCrLf & _
                  "Author: " & .Item("Author").Value & vbCrLf & _
                  "Creation Date: " & .Item("Creation Date").Value & vbCrLf & _
                  "Last Save Time: " & .Item("Last Save Time").Value
    End With
    
    MsgBox message, vbInformation, "Read Properties"

End Sub

Code Explanation

Workbook properties are stored in the BuiltinDocumentProperties collection of the Workbook object.

You can retrieve the value of a specific property using the syntax .Item("Property Name").Value. The most important point here is that you must specify the property name in English, even if you are using a non-English version of Excel.

Common Readable Property Names:

• Title
• Subject
• Author
• Keywords
• Comments
• Creation Date
• Last Author
• Last Save Time

Setting (Writing) Workbook Properties

Next, let’s look at how to write information to the workbook properties from VBA. This is useful for purposes such as automatically setting the author of a report generated by a macro.

Completed VBA Code

Sub WriteDocumentProperties()

    ' Declare variables
    Dim targetBook As Workbook
    Set targetBook = ThisWorkbook
    
    ' Work with BuiltinDocumentProperties using a With block
    With targetBook.BuiltinDocumentProperties
        ' Set values for writable properties
        .Item("Title").Value = "Monthly Sales Report"
        .Item("Subject").Value = "Auto-generated by VBA"
        .Item("Author").Value = "Sales Dept Automation Macro"
        .Item("Comments").Value = "This file was auto-generated on " & Now()
    End With
    
    ' You need to save the workbook to apply property changes
    If targetBook.Saved = False Then
        ' targetBook.Save ' Uncomment to save if necessary
    End If

    MsgBox "Workbook properties have been set. Please check File > Info.", vbInformation

End Sub

Code Explanation

To set (write) a property value, use the same syntax as reading: .Item("Property Name").Value = "Value you want to set"

Important Notes:

1. Read-Only Properties: Some properties, such as “Creation Date” and “Last Save Time,” are read-only. Trying to write a value to them will result in an error.
2. Saving: Changes made to property values via VBA are not reflected in the file until the workbook is saved.

Summary

You can manipulate workbook properties through the BuiltinDocumentProperties collection.

• Read: myValue = myBook.BuiltinDocumentProperties.Item("PropertyName").Value
• Write: myBook.BuiltinDocumentProperties.Item("PropertyName").Value = "NewValue"

Remember to always specify the property names in English. Managing file properties with VBA allows you to improve file searchability and clarify the origin of reports generated by macros, enhancing the quality of your document management.