CustomDocumentProperties problem...

[Jens Meier]

Hello ng!

I am having some trouble with a CustomDocumentProperty I try to define in an
Excel workbook.
As the code is very simple, here I go.
The first worksheet contains two named ranges, called "testfeld1" (contains
value: "5") and "testfeld2" (contains value: "hello world").

=====================
Private Sub TestCDP()
If ActiveWorkbook.CustomDocumentProperties.Count > 0 Then
ActiveWorkbook.CustomDocumentProperties("CDP").Delete
ActiveWorkbook.CustomDocumentProperties("CDP2").Delete
End If

ActiveWorkbook.CustomDocumentProperties.Add Name:="CDP", _
Type:=msoPropertyTypeNumber, _
LinkToContent:=True, LinkSource:="testfeld"
ActiveWorkbook.CustomDocumentProperties.Add Name:="CDP2", _
Type:=msoPropertyTypeString, _
LinkToContent:=True, LinkSource:="testfeld2"

DisplayPropertyInfo (ActiveWorkbook.CustomDocumentProperties("CDP"))
DisplayPropertyInfo (ActiveWorkbook.CustomDocumentProperties("CDP2"))
End Sub

Sub DisplayPropertyInfo(dp As DocumentProperty)
MsgBox "value = " & dp.Value & Chr(13) & _
"type = " & dp.Type & Chr(13) & _
"name = " & dp.Name
End Sub
=====================

As a result, I get the following values:
CDP = 0
CDP2 = ?????????????????????????????????????????????????????????????

However, if I access the CDPs via the menu (File/Properties), I can see the
correct values etc.

Any guess what's wrong?

Thanks a lot!
Jens

 

[Bob Phillips]

Jens,

It appears that when you used a LinkedSource, the documentproperties doesn't
actually hold the value (which makes sense), but picks it up from the
LinkedSource. When you look in properties, it looks up that value.

In your code, you need to do the same

Sub DisplayPropertyInfo(dp As DocumentProperty)
MsgBox "value = " & Range(dp.LinkSource).Value & Chr(13) & _
"type = " & dp.Type & Chr(13) & _
"name = " & dp.Name
End Sub

 

[Jens Meier]

It appears that when you used a LinkedSource, the documentproperties doesn't
actually hold the value (which makes sense), but picks it up from the
LinkedSource. When you look in properties, it looks up that value.

In your code, you need to do the same

Sub DisplayPropertyInfo(dp As DocumentProperty)
MsgBox "value = " & Range(dp.LinkSource).Value & Chr(13) & _
"type = " & dp.Type & Chr(13) & _
"name = " & dp.Name
End Sub

Hi Bob,

thanks for your code snippet! This way, I get it to work.

However, if I always have to VBA to look up the value of the linked range,
then what do I actually need CDPs for?
I mean, I could also store variable values between two Excel sessions
directly into some Excel cell, and then specify in Workbook_Open something
like:
blnMyBoolean = Range("MyRange").Value

Wouldn't that do just the same without having to use CDPs?

Thanks again!
Jens

 

[Bob Phillips]

You don't have to use VBA, as you noticed, the correct value is returned
when you looked up the property in Excel. If you want to manipulate the
value in VBA, then indeed you do have to use VBA, but that is self-evident
isn't it, not peculiar to this situation. You could save it in a worksheet,
but you have to take up worksheet space with that, document properties you
don't.

It is all really a matter of appropriateness IMO, are you just trying to
store a value, if so use a worksheet or an Excel name, or are you defining
an attribute of the workbook, such as your company which owns it or
somesuch, in which case use the properties. The other thing to remember is
that is you use the properties, you can see those from Windows Explorer
without opening the spreadsheet.

 

[Jens Meier]

You don't have to use VBA, as you noticed, the correct value is returned
when you looked up the property in Excel. If you want to manipulate the
value in VBA, then indeed you do have to use VBA, but that is self-evident
isn't it, not peculiar to this situation. You could save it in a worksheet,
but you have to take up worksheet space with that, document properties you
don't.

It is all really a matter of appropriateness IMO, are you just trying to
store a value, if so use a worksheet or an Excel name, or are you defining
an attribute of the workbook, such as your company which owns it or
somesuch, in which case use the properties. The other thing to remember is
that is you use the properties, you can see those from Windows Explorer
without opening the spreadsheet.

Bob,

thanks for your answer!

Regards,
Jens