VB and VBA Users Source Code: Setting and getting the values of CustomDocumentProperties in Excel
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Setting and getting the values of CustomDocumentProperties in Excel
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, June 25, 2002
Hits:
1643
Category:
Unspecified
Article:
The following code can be used to set and get the values of custom document properties in Excel Workbooks. This could be used to automatically update a "VersionNumber" property eg: Call CustomDocumentSetValue("VersionNumber", CustomDocumentGetValue("VersionNumber", 0) + 1) Note, custom document properties can be read without opening a file (look at the file properties in Explorer). 'Purpose : Sets a custom document property in an Excel Workbook 'Inputs : sProperty The name of the custom document property to set the value of. ' vValue The value to set the property to. ' [oWkb] The workbook to set the property in (default to this workbook). 'Outputs : Returns True on success 'Author : Andrew Baker (copyright www.vbusers.com) 'Date : 08/Jul/2000 'Notes : Function CustomDocumentSetValue(sProperty As String, vValue As Variant, Optional oWkb As Workbook) As Boolean Dim tType As Long, vExistingValue As Variant On Error GoTo ErrFailed 'Get reference to workbook If oWkb Is Nothing Then Set oWkb = ThisWorkbook End If 'Select document type Select Case VarType(vValue) Case vbBoolean tType = msoPropertyTypeBoolean Case vbString tType = msoPropertyTypeString Case vbDate tType = msoPropertyTypeDate Case vbLong, vbInteger tType = msoPropertyTypeNumber Case vbSingle, vbDouble tType = msoPropertyTypeFloat End Select On Error Resume Next 'Check if property exists If oWkb.CustomDocumentProperties(sProperty) Is Nothing = True Then 'Doesn't exist, add new property On Error GoTo ErrFailed oWkb.CustomDocumentProperties.Add sProperty, False, tType, vValue Else 'Exists, update existing property On Error GoTo ErrFailed oWkb.CustomDocumentProperties(sProperty).Value = vValue End If Exit Function ErrFailed: Debug.Print "Error in CustomDocumentSetValue: " & Err.Description CustomDocumentSetValue = False End Function 'Purpose : Reads a custom document property value in an Excel Workbook 'Inputs : sProperty The name of the custom document property to get the value of. ' [vDefault] The default value to return if the property doesn't exist. ' [oWkb] The workbook to get the property from (default to this workbook). 'Outputs : Returns the value of the property 'Author : Andrew Baker (copyright www.vbusers.com) 'Date : 08/Jul/2000 'Notes : Function CustomDocumentGetValue(sProperty As String, Optional vDefault As Variant, Optional oWkb As Workbook) As Variant On Error GoTo ErrDefault 'Get reference to workbook If oWkb Is Nothing Then Set oWkb = ThisWorkbook End If 'Return value CustomDocumentGetValue = oWkb.CustomDocumentProperties(sProperty).Value Exit Function ErrDefault: 'Item doesn't exist, return default value CustomDocumentGetValue = vDefault Err.Clear End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
