Workbook Properties

Unlike Word, there is no concept of Workbook Variables, only Workbook Properties.


Workbook Built-in Properties

ActiveWorkbook.BuiltinDocumentProperties.Item("Subject") 
ActiveWorkbook.BuiltinDocumentProperties("Subject")
ActiveWorkbook.BuiltinDocumentProperties.Item(2)
ActiveWorkbook.BuiltinDocumentProperties(2)

This returns a collection that represents all the built-in document properties for the specified workbook.
You can refer to built-in document properties either by index value (1 based) or by name.
The following list shows the available built-in document property names.
There is no enumeration like there is in Word.

Title1You cannot change this property before a document is saved to display a "suggested" file name
Subject2 
Author3 
Keywords4 
Comments5 
Template6 
Last Author7 
Revision Number8 
Application Name9 
Last Print Date10 
Creation Date11 
Last Save Time12 
Total Editing Time13 
Number of Pages14 
Number of Words15 
Number of Characters16 
Security17 
Category18 
Format19 
Manager20 
Company21 
Number of Bytes22 
Number of Lines23 
Number of Paragraphs24 
Number of Slides25 
Number of Notes26 
Number of Hidden Slides27 
Number of Multimedia Clips28 
Hyperlink base29 
Number of Characters (with spaces)30 

Workbook Custom Properties

objDocument.CustomDocumentProperties 

Public Sub DocumentProperties() 
Dim icount As Integer
Dim objDocumentProperty As DocumentProperty

   For icount = 1 To ActiveWorkbook.BuiltinDocumentProperties.Count
      Set objDocumentProperty = ActiveWorkbook.BuiltinDocumentProperties.Item(icount)

      Debug.Print objDocumentProperty.Name
      Debug.Print objDocumentProperty.LinkSource
      Debug.Print objDocumentProperty.LinkToContent
      Debug.Print objDocumentProperty.Parent
      Debug.Print objDocumentProperty.Type
      Debug.Print objDocumentProperty.Value
   Next icount
End Sub

Name - Specifies the property name
Type - The type of the property
Value - This is current setting


With the properties LinkSource and LinkToContent the value of a custom property can be linked directly to the contents of a worksheets
LinkSource - Gets or sets the source of a linked custom document property. The LinkToContent the value of a custom property can be linked directly to the contents of a worksheets
LinkToContent - Is True if the value of the custom document property is linked to the content of the container document. False if the value is static. This must be set to True and LinkSource must be a named range to the cell.


Workbook Custom Properties

ActiveWorkbook.CustomDocumentProperties 

This property returns the entire collection of custom document properties.
You can only refer to the custom document properties by their name. You cannot use a 1-based index value.
Use the Item method to return a single member of the collection (a DocumentProperty object) by specifying either the name of the property or the collection index (as a number).

CustomDocumentProperties.Item("BetterSolutions") 
CustomDocumentProperties("BetterSolutions")

msoPropertyTypeString


This will extract all the custom properties from a workbook

Public Sub CustomProperties_Extract() 
Dim icount As Integer
Dim objDocumentProperty As DocumentProperty
   For icount = 1 To ActiveWorkbook.CustomDocumentProperties.Count
      Set objDocumentProperty = ActiveWorkbook.CustomDocumentProperties.Item(icount)
      ThisWorkbook.Worksheets("Sheet1").Range("A" & icount).Value = objDocumentProperty.Name
      ThisWorkbook.Worksheets("Sheet1").Range("B" & icount).Value = objDocumentProperty.Value
      ThisWorkbook.Worksheets("Sheet1").Range("C" & icount).Value = objDocumentProperty.Type
   Next icount
End Sub

This will import a list of custom properties into a workbook

Public Sub CustomProperties_Import() 
Dim lrowlast As Long
Dim lrowno As Long
   lrowlast = ThisWorkbook.Worksheets("Sheet1").Range("A1").SpecialCells(xlLastCell).Row
   For lrowno = 1 To lrowlast
      Call Workbooks("ISP.xlsx").CustomDocumentProperties.Add( _
         Name:=ThisWorkbook.Worksheets("Sheet1").Range("A" & lrowno).Value, _
         LinkToContent:=Office.MsoTriState.msoFalse, _
         Value:=ThisWorkbook.Worksheets("Sheet1").Range("B" & lrowno).Value, _
         Type:=Office.MsoDocProperties.msoPropertyTypeString)
   Next lrowno
End Sub

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext