Editing Excel's Built-In Document Properties

In Document Properties, Excel MVP Andrew Engwirda provides some code for listing the built-in document properties in Excel.

So I went ahead and wrote some VBA code that would let you update your properties programmatically.

Version 1

Function ChangeDocProp(DocProp As String, NewVal As String) As Boolean
  On Error Goto ExitProc
  ActiveWorkbook.BuiltinDocumentProperties.Item(DocProp) = NewVal

' assume success
ChangeDocProp = True
Exit Function
ExitProc:
ChangeDocProp = False
End Function

DocProp is any of the following values:

  • Title
  • Subject
  • Author
  • Keywords
  • Comments
  • Template
  • Last author
  • Revision number
  • Application name
  • Last print date
  • Creation date
  • Last save time
  • Total editing time
  • Number of pages
  • Number of words
  • Number of characters
  • Security
  • Category
  • Format
  • Manager
  • Company
  • Number of bytes
  • Number of lines
  • Number of paragraphs
  • Number of slides
  • Number of notes
  • Number of hidden Slides
  • Number of multimedia clips
  • Hyperlink base
  • Number of characters (with spaces)
  • Content type
  • Content status
  • Language
  • Document version

The last four properties are Excel 2007 only, so if you are using Excel 2000-2003, stick to the first 30.

Usage:

Sub testme()
Dim success As Boolean
success = ChangeDocProp("Author", "JP")
End Sub

The function returns TRUE if it succeeded, FALSE if not.

Here's an alternate version that uses enums to create Intellisense for the document property you want to edit. It's important that this code be placed at the top of a standard module, so that the Enum section is at the very top.

Version 2

Public Enum BuiltInDocProps
Title = 1
Subject
Author
Keywords
Comments
Template
Last_author
Revision_Number
Application_Name
Last_print_date
Creation_date
Last_save_time
Total_editing_time
Number_of_pages
Number_of_words
Number_of_characters
Security
Category
Format_
Manager
Company
Number_of_bytes
Number_of_lines
Number_of_paragraphs
Number_of_slides
Number_of_notes
Number_of_hidden_Slides
Number_of_multimedia_clips
Hyperlink_base
Number_of_characters_with_spaces
Content_type
Content_status
Language
Document_version
End Enum

Function ChangeDocProp(DocProp As BuiltInDocProps, NewVal As String) As Boolean

On Error GoTo ExitProc
ActiveWorkbook.BuiltinDocumentProperties.Item(DocProp) = NewVal

' assume success
ChangeDocProp = True
Exit Function

ExitProc:
ChangeDocProp = False
End Function

This code works almost exactly the same as Version 1, but the enum creates Intellisense so once you type "ChangeDocProp(" you'll get a dropdown list of each of the available built-in document properties. Very cool and makes typos nearly impossible.

Usage:

Sub testme()
Dim success As Boolean
success = ChangeDocProp(Author, "JP")
End Sub

Notice the property name is not in quotes in this version. Of course you'll probably want a more robust macro, such as one that updates all of the files in a folder. The following late-bound VBA code works with Version 2 of the ChangeDocProp code found above (including the Enum section).

Function UpdateFiles(strFolder As String, DocProp As BuiltInDocProps, _
newVal As String) As Boolean

On Error GoTo ExitProc

Dim fso As Object ' Scripting.FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

' loop through files in given folder
Dim filen As Object
Dim wkbk As Excel.Workbook
Application.ScreenUpdating = False
For Each filen In fso.GetFolder(strFolder).Files
  If InStr(UCase$(filen.Name), "XLS") > 0 Then ' it's a spreadsheet
    Set wkbk = Workbooks.Open(strFolder & filen.Name)
    Call ChangeDocProp(DocProp, newVal)
    wkbk.Close True
  End If
Next filen

Application.ScreenUpdating = True
' assume success
UpdateFiles = True
Exit Function

ExitProc:
UpdateFiles = False
End Function

So let's say you keep all your company spreadsheets on a network folder 'H:' and you need to change the author name because someone left the company. Or your company just changed its name, and you have hundreds of spreadsheets with your old company name in them.

With this macro you just specify the folder, the property you want to change, and its new value, and it fixes all of the spreadsheets in that folder.

Usage:

Sub testme()
Dim success As Boolean
success = UpdateFiles("H:\", Author, "JP")
End Sub
About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 17 Comment(s) on Editing Excel's Built-In Document Properties:

  1. @JP: I like the work you've done – so I adopted Version 2. I've added a GetDocProp (read a property) and SetDocProp (set a variable to a document property) using the same enumeration structure.

    Two issues:
    1. The enumberation element "Format" caused compile errors (conflict with the VBA format function) in my code so I commented it out.
    2. #1 was a bad idea, of course, since the enumerations resolve to integer index values, and any docprop greater than "Format" was incorrectly referenced. Resolved that by using "VBA.Format" for the format function.

    Any idea why there is a conflict for the "Format" enumeration element?

    • This is probably happening because Format is a reserved word in VBA. In fact, if you leave it as Format and try to use the actual reserved word "Format" elsewhere in the same project, you'll get errors.

      The word in the enumeration actually doesn't matter, so you can change it to anything. I'll update the code above.

  2. Thanks

  3. Gerrit writes:

    Your code is exactly what I was looking for.
    Thanks a lot!!!
    Regards Gerrit

  4. Ang Poh writes:

    Do you have any tipes to write/read Document Properties from the server side?

    Bascially, I would like to be able to access the document properties stored in a share-point server via EXCEL VBA.

    Any pointers would be greatly apprecaited.

  5. hi,

    i am looking some thing where in i can use excel VBA codes and get output like say, want to update below properties :

    -Author
    -Manager
    -Company
    (Above already updated and want to change through codes)

    and
    -Custom Properties (Name, Value and Type)

    appreciate if you can provide me ideal codes.

    • Check out the code in the blog post, one of the examples is exactly one you've asked about. If you want to update the Author of an Excel worksheet, call the code as I did in the "Usage" sections listed below each procedure. You'll have to adapt the code to your particular situation, however.

  6. thanks but how i can update
    -Custom Properties (Name, Value and Type)
    via codes
    are there any specific pre defined codes which i can use directly.

    Rgds

  7. There's a great example at Microsoft. Check it out.

  8. This code works for me except I'm having issues with Revision Number. It will not let me update to anything with a decimal number, only integer. Even putting it in as a String "2.1" it returns False however I can input "2" and it will update. Any suggestions?

    • Sure, use only whole numbers.

      • Unfortunately these spreadsheets are uploaded to a Legacy app that is looking specifically for 2.0, it won't look at 2 the same way and rejects the spreadsheet. For some reason, Excel 2010 has a "feature" that changes Revision number back to "1" everytime you save the file!

    • By the way, I should add that you CAN use decimal numbers, but only as a custom document property. Just add a custom property and call it whatever you want. (I'll call mine "Version"). Make sure the Type is Number, and you can use decimal numbers with the new custom property. i.e.

      ActiveWorkbook.CustomDocumentProperties("Version") = "2.1"

      will work.

      • Thanks for the feedback, I will try and work with the custom properties to see if I can trick the Legacy app that's pulling in the spreadsheet. This is something buggy in Excel 2010 because we were using 2003 and it let you change the Revision Number to decimal and didn't revert back everytime you save the file.

  9. JohnathanL writes:

    Is there a way to have document properties set based on a cell in a worksheet? Example I have a field that will retun a value and I would like for the Document Property to equal what the value is in the cell.

  10. Is there a way to retrieve the document contents of the excel properties. I want to pull the worksheet tab names that are within the file.

This article is closed to any future comments.
Random Data Generator