Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CustomDocumentProperties problem...
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").Del ete ActiveWorkbook.CustomDocumentProperties("CDP2").De lete 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CustomDocumentProperties problem...
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 -- HTH Bob Phillips "Jens Meier" wrote in message ... 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").Del ete ActiveWorkbook.CustomDocumentProperties("CDP2").De lete 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CustomDocumentProperties problem...
"Bob Phillips" schrieb im Newsbeitrag
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CustomDocumentProperties problem...
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. -- HTH Bob Phillips "Jens Meier" wrote in message ... "Bob Phillips" schrieb im Newsbeitrag 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CustomDocumentProperties problem...
"Bob Phillips" schrieb im Newsbeitrag
... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Adding and Changing 'CustomDocumentProperties' from VBA on other f | Excel Programming | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming | |||
CustomDocumentProperties | Excel Programming |