Reading metadata from within Microsoft Office VBA
Table of contents
| If you have come here looking for information about MS Word or Excel metadata generally, please click here for more relevant information. |
If you want to import Word or Excel document properties into a spreadsheet or a table, then please download a free 7-day trial of Filecats Professional (if you have Excel) or Filecats Metadata (if you don’t).
In this article, I will be discussing how you can access these file properties in VBA.
Opening VBA
Visual Basic for Applications (VBA) allows you to access Microsoft Office’s document properties (also known as metadata). However, you first need to know the exact name of the properties to be able to access it.
You can obtain the names of these attributes by first opening Visual Basic for Applications (VBA) in Microsoft Office. This can be done by:
- Pressing F11,
- Going to Developer – Visual Basic (in Office 2007 or greater, including Office 365), or
- If available, going to View – Macros – View Macros – Edit (in Office 2007 or greater, including Office 365).
(If you cannot see the Developer tab, go to File – Options – Customize Ribbons, and check the box marked “Developer” in the right-hand column.)
[Best_Wordpress_Gallery id=”14″ gal_title=”MicrosoftWordVBA”]
Then open the Immediate window (press Ctrl+G if you cannot see it) and type the following (on one line):
For Each metadat In ActiveDocument.BuiltInDocumentProperties: Debug.Print metadat.Name: Next
The above code is for Microsoft Word:
- For Microsoft Excel, change ActiveDocument to ActiveWorkbook.
- For Microsoft Powerpoint, change ActiveDocument to ActivePresentation.
Metadata Fields
The results may vary according to the program and the version of the program (but not according to the language – the Spanish version of Excel gives the same results as the English version). Microsoft Word 2010 says:
- 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
You may notice that not all of the above of actually relevant to Microsoft Word (for example, “number of slides”).
You can then query the current document’s properties by typing in the immediate window (press Ctrl+G if you don’t see it) something like:
Debug.Print ActiveDocument.BuiltInDocumentProperties(“Author”)
This will give the Author of the current document. Again, “ActiveDocument” should be changed as above (depending whether you are in Microsoft Excel, PowerPoint etc.).
Certain of the properties are also editable, and you can do that by syntax such as:
ActiveDocument.BuiltInDocumentProperties(“Author”) = “New person”
It would make no sense for all of the properties to be directly editable. For example, editing the number of words from 100 to 50 is not something which should be by altering a number, but by editing the text.
Bear in mind also that editing one property may alter others. For example, if you edit the Author as above, it will only be committed to the document permanently if you save the document. However, doing that adjusts the Last Saved Date, and may also change the “Last Author” field (which is different to the “Author” field).
What’s next?
Having discussed how to access metadata for one file, you may want to know how to accses it for multiple files. The next article discusses the pros and cons on how to do catalog in VBA, and other ways.
Alternatively, you may be curious as to how these document properties are actually used in practice. This article shows the results of an investigation of nearly 1,000 Word documents.
You may also be curious about document properties for photographs and pictures, GPS data, audio and video files, and Outlook files exported to MSG (such as emails). Those articles discuss these, but this article shows an overview.
Finally, you may want to use a specialised metadata extractor, as that does not require opening the files. Filecats Professional can import file data into Microsoft Excel, and Filecats Metadata extracts the metadata into its own table, so you can copy and paste it elsewhere. There’s a free 7-day trial. Download them here.
Thank you for reading
Was this article of use to you? Then please like our Twitter or Facebook account using the buttons to the left, or share this page using the buttons to the right. It does make a difference – Thanks!
Want more articles? Click here.
Did you know that the above in your folders and subfolders can be cataloged in Microsoft Excel with just a few clicks? Download a 7-day free trial of Filecats Professional or Filecats Metadata today. (More information about these metadata extractors.)
