dsolefile - error handling when value does not exist (in excel)

Question

I'm trying to update document properties and create new entries if they don't exist

However this type of thing does not work

Set objDocProps = DSO.GetDocumentProperties(sfilename:=FileName)

With objDocProps
If .CustomProperties("ABC") Is Nothing Then
'create it here

and if I put an error handler in there it barfs as either being locked or having lost connection

errhandler:
Select Case Err.Number
 Case -2147220987 ' missing custom property
 Debug.Print "missing custom property"
 With objDocProps
     .CustomProperties("ABC").Value = "banana!"

Answer 1

Could you use the CustomDocumentProperties collection for the appropriate Excel workbook instead? You could then just iterate through the collection and edit the property if you find it. If it is not present you could then create the property

Answer 2

There appear to be issues when trying to access CustomProperties by name.

The solution I have implemented is to iterate the CustomPropery collection to determine the index of the item (if it exists), then use this to set the value (or add a new one if it does not)

Pass in: your custom properties object, the entry you wish to populate and the value you wish to populate it with

Sub UpsertEntry(objCustomProps, entryname, entryvalue)
  'update the custom property with value supplied
  On Error Resume Next

  Dim icount 
  Dim iindex 

  For icount = 1 To objCustomProps.Count

    If objCustomProps.Item(icount).name = entryname Then
      iindex = icount
      Exit For
    Else
      iindex = 0
    End If

  Next


  If iindex = 0 Then 'no custom property found

   objCustomProps.Add entryname, entryvalue
   Wscript.Echo " Adding   [" & entryname & ":" & entryvalue & "]"
  Else
   objCustomProps.Item(iindex).Value = entryvalue
   Wscript.Echo " Changing [" & entryname & ":" & entryvalue & "]"

  End If
  On Error GoTo 0


End Sub