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.
Title | 1 | You cannot change this property before a document is saved to display a "suggested" file name |
Subject | 2 | |
Author | 3 | |
Keywords | 4 | |
Comments | 5 | |
Template | 6 | |
Last Author | 7 | |
Revision Number | 8 | |
Application Name | 9 | |
Last Print Date | 10 | |
Creation Date | 11 | |
Last Save Time | 12 | |
Total Editing Time | 13 | |
Number of Pages | 14 | |
Number of Words | 15 | |
Number of Characters | 16 | |
Security | 17 | |
Category | 18 | |
Format | 19 | |
Manager | 20 | |
Company | 21 | |
Number of Bytes | 22 | |
Number of Lines | 23 | |
Number of Paragraphs | 24 | |
Number of Slides | 25 | |
Number of Notes | 26 | |
Number of Hidden Slides | 27 | |
Number of Multimedia Clips | 28 | |
Hyperlink base | 29 | |
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