Custom properties not showing the value when set programmatically

Hi,

When I create a CustomDocumentProperty programmatically and set the
LinkToContent value to True, the value is not available to use
programmatically before I have accessed the menu File->Properties.
To illustrate it I have included some sample code, just create a new
workbook and add a button to it. Paste the following code into the
"ThisWorkbook"

Private Sub Workbook_Open()
ActiveSheet.Range("A1").Value = "Test"
ActiveSheet.Names.Add Name:="TestName", RefersTo:="=Sheet1!$A$1"
ActiveWorkbook.CustomDocumentProperties.Add Name:="CustomProp",
LinkToContent:=True, Type:=msoPropertyTypeString, LinkSource:="TestName"
End Sub

Add the following code into the button event handler
MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value

Is there a way to get Excel to populate the CustomDocumentProperties
programmatically, We are integrating all our documents with Meridio which is
a EDRM solution and this program use the DocumentProperties when storing the
documents

Regards
Jan Petter

Hi Jon,

I replicate what you describe in XL2000, can't see an obvious solution
without flashing the file properties dialog.

After showing the dialog .Value returns as expected. If I now change the
value in the linked cell A1, .Value returns the old value, need to see the
dialog again for it to be updated.

FWIW, after defining the custom property and not seeing the dialog, the
returned value appears to be 122 bytes of meaningless (to me) numbers.

dim arr() as byte
arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value

' arr > 0 to 121

Regards,
Peter T

"Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> Hi,
>
> When I create a CustomDocumentProperty programmatically and set the
> LinkToContent value to True, the value is not available to use
> programmatically before I have accessed the menu File->Properties.
> To illustrate it I have included some sample code, just create a new
> workbook and add a button to it. Paste the following code into the
> "ThisWorkbook"
>
> Private Sub Workbook_Open()
> ActiveSheet.Range("A1").Value = "Test"
> ActiveSheet.Names.Add Name:="TestName", RefersTo:="=Sheet1!$A$1"
> ActiveWorkbook.CustomDocumentProperties.Add Name:="CustomProp",
> LinkToContent:=True, Type:=msoPropertyTypeString, LinkSource:="TestName"
> End Sub
>
> Add the following code into the button event handler
> MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
>
> Is there a way to get Excel to populate the CustomDocumentProperties
> programmatically, We are integrating all our documents with Meridio which
is
> a EDRM solution and this program use the DocumentProperties when storing
the
> documents
>
> Regards
> Jan Petter
>
>

This problem exists in the XL2003 SP2 version also.

We have a lot of users that will use the excel spreadsheets and it can’t bee
expected that they will access the File->properties menu before they save the
workbooks.
So if a solution or workaround for this problem exists I would really like
to hear about it.

Jan Petter


"Peter T" wrote:

> Hi Jon,
>
> I replicate what you describe in XL2000, can't see an obvious solution
> without flashing the file properties dialog.
>
> After showing the dialog .Value returns as expected. If I now change the
> value in the linked cell A1, .Value returns the old value, need to see the
> dialog again for it to be updated.
>
> FWIW, after defining the custom property and not seeing the dialog, the
> returned value appears to be 122 bytes of meaningless (to me) numbers.
>
> dim arr() as byte
> arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
>
> ' arr > 0 to 121
>
> Regards,
> Peter T
>
> "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > Hi,
> >
> > When I create a CustomDocumentProperty programmatically and set the
> > LinkToContent value to True, the value is not available to use
> > programmatically before I have accessed the menu File->Properties.
> > To illustrate it I have included some sample code, just create a new
> > workbook and add a button to it. Paste the following code into the
> > "ThisWorkbook"
> >
> > Private Sub Workbook_Open()
> > ActiveSheet.Range("A1").Value = "Test"
> > ActiveSheet.Names.Add Name:="TestName", RefersTo:="=Sheet1!$A$1"
> > ActiveWorkbook.CustomDocumentProperties.Add Name:="CustomProp",
> > LinkToContent:=True, Type:=msoPropertyTypeString, LinkSource:="TestName"
> > End Sub
> >
> > Add the following code into the button event handler
> > MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> >
> > Is there a way to get Excel to populate the CustomDocumentProperties
> > programmatically, We are integrating all our documents with Meridio which
> is
> > a EDRM solution and this program use the DocumentProperties when storing
> the
> > documents
> >
> > Regards
> > Jan Petter
> >
> >
>
>
>

Jan,
Does this help ?

NickHK

"Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> This problem exists in the XL2003 SP2 version also.
>
> We have a lot of users that will use the excel spreadsheets and it can't
bee
> expected that they will access the File->properties menu before they save
the
> workbooks.
> So if a solution or workaround for this problem exists I would really like
> to hear about it.
>
> Jan Petter
>
>
> "Peter T" wrote:
>
> > Hi Jon,
> >
> > I replicate what you describe in XL2000, can't see an obvious solution
> > without flashing the file properties dialog.
> >
> > After showing the dialog .Value returns as expected. If I now change the
> > value in the linked cell A1, .Value returns the old value, need to see
the
> > dialog again for it to be updated.
> >
> > FWIW, after defining the custom property and not seeing the dialog, the
> > returned value appears to be 122 bytes of meaningless (to me) numbers.
> >
> > dim arr() as byte
> > arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> >
> > ' arr > 0 to 121
> >
> > Regards,
> > Peter T
> >
> > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > Hi,
> > >
> > > When I create a CustomDocumentProperty programmatically and set the
> > > LinkToContent value to True, the value is not available to use
> > > programmatically before I have accessed the menu File->Properties.
> > > To illustrate it I have included some sample code, just create a new
> > > workbook and add a button to it. Paste the following code into the
> > > "ThisWorkbook"
> > >
> > > Private Sub Workbook_Open()
> > > ActiveSheet.Range("A1").Value = "Test"
> > > ActiveSheet.Names.Add Name:="TestName", RefersTo:="=Sheet1!$A$1"
> > > ActiveWorkbook.CustomDocumentProperties.Add Name:="CustomProp",
> > > LinkToContent:=True, Type:=msoPropertyTypeString,
LinkSource:="TestName"
> > > End Sub
> > >
> > > Add the following code into the button event handler
> > > MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > >
> > > Is there a way to get Excel to populate the CustomDocumentProperties
> > > programmatically, We are integrating all our documents with Meridio
which
> > is
> > > a EDRM solution and this program use the DocumentProperties when
storing
> > the
> > > documents
> > >
> > > Regards
> > > Jan Petter
> > >
> > >
> >
> >
> >

No, not really. See my previous response

Jan Petter

"NickHK" wrote:

> Jan,
> Does this help ?
>
> NickHK
>
> "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> > This problem exists in the XL2003 SP2 version also.
> >
> > We have a lot of users that will use the excel spreadsheets and it can't
> bee
> > expected that they will access the File->properties menu before they save
> the
> > workbooks.
> > So if a solution or workaround for this problem exists I would really like
> > to hear about it.
> >
> > Jan Petter
> >
> >
> > "Peter T" wrote:
> >
> > > Hi Jon,
> > >
> > > I replicate what you describe in XL2000, can't see an obvious solution
> > > without flashing the file properties dialog.
> > >
> > > After showing the dialog .Value returns as expected. If I now change the
> > > value in the linked cell A1, .Value returns the old value, need to see
> the
> > > dialog again for it to be updated.
> > >
> > > FWIW, after defining the custom property and not seeing the dialog, the
> > > returned value appears to be 122 bytes of meaningless (to me) numbers.
> > >
> > > dim arr() as byte
> > > arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > >
> > > ' arr > 0 to 121
> > >
> > > Regards,
> > > Peter T
> > >
> > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > > Hi,
> > > >
> > > > When I create a CustomDocumentProperty programmatically and set the
> > > > LinkToContent value to True, the value is not available to use
> > > > programmatically before I have accessed the menu File->Properties.
> > > > To illustrate it I have included some sample code, just create a new
> > > > workbook and add a button to it. Paste the following code into the
> > > > "ThisWorkbook"
> > > >
> > > > Private Sub Workbook_Open()
> > > > ActiveSheet.Range("A1").Value = "Test"
> > > > ActiveSheet.Names.Add Name:="TestName", RefersTo:="=Sheet1!$A$1"
> > > > ActiveWorkbook.CustomDocumentProperties.Add Name:="CustomProp",
> > > > LinkToContent:=True, Type:=msoPropertyTypeString,
> LinkSource:="TestName"
> > > > End Sub
> > > >
> > > > Add the following code into the button event handler
> > > > MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > >
> > > > Is there a way to get Excel to populate the CustomDocumentProperties
> > > > programmatically, We are integrating all our documents with Meridio
> which
> > > is
> > > > a EDRM solution and this program use the DocumentProperties when
> storing
> > > the
> > > > documents
> > > >
> > > > Regards
> > > > Jan Petter
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Jan,
Forgot the link....

Does this help ?
http://www.cpearson.com/excel/docprop.htm
NickHK


"Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> This problem exists in the XL2003 SP2 version also.
>
> We have a lot of users that will use the excel spreadsheets and it can't
bee
> expected that they will access the File->properties menu before they save
the
> workbooks.
> So if a solution or workaround for this problem exists I would really like
> to hear about it.
>
> Jan Petter
>
>
> "Peter T" wrote:
>
> > Hi Jon,
> >
> > I replicate what you describe in XL2000, can't see an obvious solution
> > without flashing the file properties dialog.
> >
> > After showing the dialog .Value returns as expected. If I now change the
> > value in the linked cell A1, .Value returns the old value, need to see
the
> > dialog again for it to be updated.
> >
> > FWIW, after defining the custom property and not seeing the dialog, the
> > returned value appears to be 122 bytes of meaningless (to me) numbers.
> >
> > dim arr() as byte
> > arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> >
> > ' arr > 0 to 121
> >
> > Regards,
> > Peter T
> >
> > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > Hi,
> > >
> > > When I create a CustomDocumentProperty programmatically and set the
> > > LinkToContent value to True, the value is not available to use
> > > programmatically before I have accessed the menu File->Properties.
> > > To illustrate it I have included some sample code, just create a new
> > > workbook and add a button to it. Paste the following code into the
> > > "ThisWorkbook"
> > >
> > > Private Sub Workbook_Open()
> > > ActiveSheet.Range("A1").Value = "Test"
> > > ActiveSheet.Names.Add Name:="TestName", RefersTo:="=Sheet1!$A$1"
> > > ActiveWorkbook.CustomDocumentProperties.Add Name:="CustomProp",
> > > LinkToContent:=True, Type:=msoPropertyTypeString,
LinkSource:="TestName"
> > > End Sub
> > >
> > > Add the following code into the button event handler
> > > MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > >
> > > Is there a way to get Excel to populate the CustomDocumentProperties
> > > programmatically, We are integrating all our documents with Meridio
which
> > is
> > > a EDRM solution and this program use the DocumentProperties when
storing
> > the
> > > documents
> > >
> > > Regards
> > > Jan Petter
> > >
> > >
> >
> >
> >

Hi,

Unfortunately the examples in the document do not help. I have changed my
code to reflect the examples and I have tried to use the “DS: OLE Document
Properties 2.0 Object Library" but with no luck

Jan Petter

"NickHK" wrote:

> Jan,
> Forgot the link....
>
> Does this help ?
> http://www.cpearson.com/excel/docprop.htm
> NickHK
>
>
> "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> > This problem exists in the XL2003 SP2 version also.
> >
> > We have a lot of users that will use the excel spreadsheets and it can't
> bee
> > expected that they will access the File->properties menu before they save
> the
> > workbooks.
> > So if a solution or workaround for this problem exists I would really like
> > to hear about it.
> >
> > Jan Petter
> >
> >
> > "Peter T" wrote:
> >
> > > Hi Jon,
> > >
> > > I replicate what you describe in XL2000, can't see an obvious solution
> > > without flashing the file properties dialog.
> > >
> > > After showing the dialog .Value returns as expected. If I now change the
> > > value in the linked cell A1, .Value returns the old value, need to see
> the
> > > dialog again for it to be updated.
> > >
> > > FWIW, after defining the custom property and not seeing the dialog, the
> > > returned value appears to be 122 bytes of meaningless (to me) numbers.
> > >
> > > dim arr() as byte
> > > arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > >
> > > ' arr > 0 to 121
> > >
> > > Regards,
> > > Peter T
> > >
> > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > > Hi,
> > > >
> > > > When I create a CustomDocumentProperty programmatically and set the
> > > > LinkToContent value to True, the value is not available to use
> > > > programmatically before I have accessed the menu File->Properties.
> > > > To illustrate it I have included some sample code, just create a new
> > > > workbook and add a button to it. Paste the following code into the
> > > > "ThisWorkbook"
> > > >
> > > > Private Sub Workbook_Open()
> > > > ActiveSheet.Range("A1").Value = "Test"
> > > > ActiveSheet.Names.Add Name:="TestName", RefersTo:="=Sheet1!$A$1"
> > > > ActiveWorkbook.CustomDocumentProperties.Add Name:="CustomProp",
> > > > LinkToContent:=True, Type:=msoPropertyTypeString,
> LinkSource:="TestName"
> > > > End Sub
> > > >
> > > > Add the following code into the button event handler
> > > > MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > >
> > > > Is there a way to get Excel to populate the CustomDocumentProperties
> > > > programmatically, We are integrating all our documents with Meridio
> which
> > > is
> > > > a EDRM solution and this program use the DocumentProperties when
> storing
> > > the
> > > > documents
> > > >
> > > > Regards
> > > > Jan Petter
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Jan,
Seems that a "ActiveWorkbook.Save" flushes the value, so it can be correctly
read. Office2K anyway.

NickHK

"Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
news:A4516A2B-2E05-454C-ACD1-12D75C40D03A@microsoft.com...
> Hi,
>
> Unfortunately the examples in the document do not help. I have changed my
> code to reflect the examples and I have tried to use the "DS: OLE Document
> Properties 2.0 Object Library" but with no luck
>
> Jan Petter
>
> "NickHK" wrote:
>
> > Jan,
> > Forgot the link....
> >
> > Does this help ?
> > http://www.cpearson.com/excel/docprop.htm
> > NickHK
> >
> >
> > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> > > This problem exists in the XL2003 SP2 version also.
> > >
> > > We have a lot of users that will use the excel spreadsheets and it
can't
> > bee
> > > expected that they will access the File->properties menu before they
save
> > the
> > > workbooks.
> > > So if a solution or workaround for this problem exists I would really
like
> > > to hear about it.
> > >
> > > Jan Petter
> > >
> > >
> > > "Peter T" wrote:
> > >
> > > > Hi Jon,
> > > >
> > > > I replicate what you describe in XL2000, can't see an obvious
solution
> > > > without flashing the file properties dialog.
> > > >
> > > > After showing the dialog .Value returns as expected. If I now change
the
> > > > value in the linked cell A1, .Value returns the old value, need to
see
> > the
> > > > dialog again for it to be updated.
> > > >
> > > > FWIW, after defining the custom property and not seeing the dialog,
the
> > > > returned value appears to be 122 bytes of meaningless (to me)
numbers.
> > > >
> > > > dim arr() as byte
> > > > arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > >
> > > > ' arr > 0 to 121
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > > > Hi,
> > > > >
> > > > > When I create a CustomDocumentProperty programmatically and set
the
> > > > > LinkToContent value to True, the value is not available to use
> > > > > programmatically before I have accessed the menu File->Properties.
> > > > > To illustrate it I have included some sample code, just create a
new
> > > > > workbook and add a button to it. Paste the following code into the
> > > > > "ThisWorkbook"
> > > > >
> > > > > Private Sub Workbook_Open()
> > > > > ActiveSheet.Range("A1").Value = "Test"
> > > > > ActiveSheet.Names.Add Name:="TestName",
RefersTo:="=Sheet1!$A$1"
> > > > > ActiveWorkbook.CustomDocumentProperties.Add
Name:="CustomProp",
> > > > > LinkToContent:=True, Type:=msoPropertyTypeString,
> > LinkSource:="TestName"
> > > > > End Sub
> > > > >
> > > > > Add the following code into the button event handler
> > > > > MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > >
> > > > > Is there a way to get Excel to populate the
CustomDocumentProperties
> > > > > programmatically, We are integrating all our documents with
Meridio
> > which
> > > > is
> > > > > a EDRM solution and this program use the DocumentProperties when
> > storing
> > > > the
> > > > > documents
> > > > >
> > > > > Regards
> > > > > Jan Petter
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi, thanks for taking the time to answer these question

Unfortunately I have to have these Document Properties set before I can do a
“Save” The reason is that we are integrating excel with Meridio which is a
document archive system. Meridio has a plug-in in excel that captures the
“Save” and “Save-As” events and it is this plug-in that pulls out the
Document properties so it can use the as metadata in the Archive system. The
problem is that this happens before the Save happens in excel and therefore
all the properties show with no values in them

Jan Petter

"NickHK" wrote:

> Jan,
> Seems that a "ActiveWorkbook.Save" flushes the value, so it can be correctly
> read. Office2K anyway.
>
> NickHK
>
> "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> news:A4516A2B-2E05-454C-ACD1-12D75C40D03A@microsoft.com...
> > Hi,
> >
> > Unfortunately the examples in the document do not help. I have changed my
> > code to reflect the examples and I have tried to use the "DS: OLE Document
> > Properties 2.0 Object Library" but with no luck
> >
> > Jan Petter
> >
> > "NickHK" wrote:
> >
> > > Jan,
> > > Forgot the link....
> > >
> > > Does this help ?
> > > http://www.cpearson.com/excel/docprop.htm
> > > NickHK
> > >
> > >
> > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> > > > This problem exists in the XL2003 SP2 version also.
> > > >
> > > > We have a lot of users that will use the excel spreadsheets and it
> can't
> > > bee
> > > > expected that they will access the File->properties menu before they
> save
> > > the
> > > > workbooks.
> > > > So if a solution or workaround for this problem exists I would really
> like
> > > > to hear about it.
> > > >
> > > > Jan Petter
> > > >
> > > >
> > > > "Peter T" wrote:
> > > >
> > > > > Hi Jon,
> > > > >
> > > > > I replicate what you describe in XL2000, can't see an obvious
> solution
> > > > > without flashing the file properties dialog.
> > > > >
> > > > > After showing the dialog .Value returns as expected. If I now change
> the
> > > > > value in the linked cell A1, .Value returns the old value, need to
> see
> > > the
> > > > > dialog again for it to be updated.
> > > > >
> > > > > FWIW, after defining the custom property and not seeing the dialog,
> the
> > > > > returned value appears to be 122 bytes of meaningless (to me)
> numbers.
> > > > >
> > > > > dim arr() as byte
> > > > > arr = ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > >
> > > > > ' arr > 0 to 121
> > > > >
> > > > > Regards,
> > > > > Peter T
> > > > >
> > > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > > > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > When I create a CustomDocumentProperty programmatically and set
> the
> > > > > > LinkToContent value to True, the value is not available to use
> > > > > > programmatically before I have accessed the menu File->Properties.
> > > > > > To illustrate it I have included some sample code, just create a
> new
> > > > > > workbook and add a button to it. Paste the following code into the
> > > > > > "ThisWorkbook"
> > > > > >
> > > > > > Private Sub Workbook_Open()
> > > > > > ActiveSheet.Range("A1").Value = "Test"
> > > > > > ActiveSheet.Names.Add Name:="TestName",
> RefersTo:="=Sheet1!$A$1"
> > > > > > ActiveWorkbook.CustomDocumentProperties.Add
> Name:="CustomProp",
> > > > > > LinkToContent:=True, Type:=msoPropertyTypeString,
> > > LinkSource:="TestName"
> > > > > > End Sub
> > > > > >
> > > > > > Add the following code into the button event handler
> > > > > > MsgBox ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > > >
> > > > > > Is there a way to get Excel to populate the
> CustomDocumentProperties
> > > > > > programmatically, We are integrating all our documents with
> Meridio
> > > which
> > > > > is
> > > > > > a EDRM solution and this program use the DocumentProperties when
> > > storing
> > > > > the
> > > > > > documents
> > > > > >
> > > > > > Regards
> > > > > > Jan Petter
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Jan,
How about creating your instance of Excel with Automation, which does not
load add-in, IIRC. Do your custom doc settings, with a save. Load the
Meridio add-in and save again.
Just an idea...
NickHK

"Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
news:32634AB9-88A2-4C1F-BBD1-5B91D185D5FC@microsoft.com...
> Hi, thanks for taking the time to answer these question
>
> Unfortunately I have to have these Document Properties set before I can do
a
> "Save" The reason is that we are integrating excel with Meridio which is a
> document archive system. Meridio has a plug-in in excel that captures the
> "Save" and "Save-As" events and it is this plug-in that pulls out the
> Document properties so it can use the as metadata in the Archive system.
The
> problem is that this happens before the Save happens in excel and
therefore
> all the properties show with no values in them
>
> Jan Petter
>
> "NickHK" wrote:
>
> > Jan,
> > Seems that a "ActiveWorkbook.Save" flushes the value, so it can be
correctly
> > read. Office2K anyway.
> >
> > NickHK
> >
> > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > news:A4516A2B-2E05-454C-ACD1-12D75C40D03A@microsoft.com...
> > > Hi,
> > >
> > > Unfortunately the examples in the document do not help. I have changed
my
> > > code to reflect the examples and I have tried to use the "DS: OLE
Document
> > > Properties 2.0 Object Library" but with no luck
> > >
> > > Jan Petter
> > >
> > > "NickHK" wrote:
> > >
> > > > Jan,
> > > > Forgot the link....
> > > >
> > > > Does this help ?
> > > > http://www.cpearson.com/excel/docprop.htm
> > > > NickHK
> > > >
> > > >
> > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > > news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> > > > > This problem exists in the XL2003 SP2 version also.
> > > > >
> > > > > We have a lot of users that will use the excel spreadsheets and it
> > can't
> > > > bee
> > > > > expected that they will access the File->properties menu before
they
> > save
> > > > the
> > > > > workbooks.
> > > > > So if a solution or workaround for this problem exists I would
really
> > like
> > > > > to hear about it.
> > > > >
> > > > > Jan Petter
> > > > >
> > > > >
> > > > > "Peter T" wrote:
> > > > >
> > > > > > Hi Jon,
> > > > > >
> > > > > > I replicate what you describe in XL2000, can't see an obvious
> > solution
> > > > > > without flashing the file properties dialog.
> > > > > >
> > > > > > After showing the dialog .Value returns as expected. If I now
change
> > the
> > > > > > value in the linked cell A1, .Value returns the old value, need
to
> > see
> > > > the
> > > > > > dialog again for it to be updated.
> > > > > >
> > > > > > FWIW, after defining the custom property and not seeing the
dialog,
> > the
> > > > > > returned value appears to be 122 bytes of meaningless (to me)
> > numbers.
> > > > > >
> > > > > > dim arr() as byte
> > > > > > arr =
ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > > >
> > > > > > ' arr > 0 to 121
> > > > > >
> > > > > > Regards,
> > > > > > Peter T
> > > > > >
> > > > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in
message
> > > > > > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > > > > > Hi,
> > > > > > >
> > > > > > > When I create a CustomDocumentProperty programmatically and
set
> > the
> > > > > > > LinkToContent value to True, the value is not available to use
> > > > > > > programmatically before I have accessed the menu
File->Properties.
> > > > > > > To illustrate it I have included some sample code, just create
a
> > new
> > > > > > > workbook and add a button to it. Paste the following code into
the
> > > > > > > "ThisWorkbook"
> > > > > > >
> > > > > > > Private Sub Workbook_Open()
> > > > > > > ActiveSheet.Range("A1").Value = "Test"
> > > > > > > ActiveSheet.Names.Add Name:="TestName",
> > RefersTo:="=Sheet1!$A$1"
> > > > > > > ActiveWorkbook.CustomDocumentProperties.Add
> > Name:="CustomProp",
> > > > > > > LinkToContent:=True, Type:=msoPropertyTypeString,
> > > > LinkSource:="TestName"
> > > > > > > End Sub
> > > > > > >
> > > > > > > Add the following code into the button event handler
> > > > > > > MsgBox
ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > > > >
> > > > > > > Is there a way to get Excel to populate the
> > CustomDocumentProperties
> > > > > > > programmatically, We are integrating all our documents with
> > Meridio
> > > > which
> > > > > > is
> > > > > > > a EDRM solution and this program use the DocumentProperties
when
> > > > storing
> > > > > > the
> > > > > > > documents
> > > > > > >
> > > > > > > Regards
> > > > > > > Jan Petter
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

As Nick says all appears to update when the file is saved. It also seems
while not saved custom properties can only be read by vba methods or similar
by automation (eg DSOFile dll doesn't seem to work with an open file).

In which case, while the file is open can you simply update the value with
LinkToContent:=False when the named cell value changes. Presumably this
non-linked custom property can then be read immediately by Meridio.

Regards,
Peter T


"Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
news:32634AB9-88A2-4C1F-BBD1-5B91D185D5FC@microsoft.com...
> Hi, thanks for taking the time to answer these question
>
> Unfortunately I have to have these Document Properties set before I can do
a
> "Save" The reason is that we are integrating excel with Meridio which is a
> document archive system. Meridio has a plug-in in excel that captures the
> "Save" and "Save-As" events and it is this plug-in that pulls out the
> Document properties so it can use the as metadata in the Archive system.
The
> problem is that this happens before the Save happens in excel and
therefore
> all the properties show with no values in them
>
> Jan Petter
>
> "NickHK" wrote:
>
> > Jan,
> > Seems that a "ActiveWorkbook.Save" flushes the value, so it can be
correctly
> > read. Office2K anyway.
> >
> > NickHK
> >
> > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > news:A4516A2B-2E05-454C-ACD1-12D75C40D03A@microsoft.com...
> > > Hi,
> > >
> > > Unfortunately the examples in the document do not help. I have changed
my
> > > code to reflect the examples and I have tried to use the "DS: OLE
Document
> > > Properties 2.0 Object Library" but with no luck
> > >
> > > Jan Petter
> > >
> > > "NickHK" wrote:
> > >
> > > > Jan,
> > > > Forgot the link....
> > > >
> > > > Does this help ?
> > > > http://www.cpearson.com/excel/docprop.htm
> > > > NickHK
> > > >
> > > >
> > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > > news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> > > > > This problem exists in the XL2003 SP2 version also.
> > > > >
> > > > > We have a lot of users that will use the excel spreadsheets and it
> > can't
> > > > bee
> > > > > expected that they will access the File->properties menu before
they
> > save
> > > > the
> > > > > workbooks.
> > > > > So if a solution or workaround for this problem exists I would
really
> > like
> > > > > to hear about it.
> > > > >
> > > > > Jan Petter
> > > > >
> > > > >
> > > > > "Peter T" wrote:
> > > > >
> > > > > > Hi Jon,
> > > > > >
> > > > > > I replicate what you describe in XL2000, can't see an obvious
> > solution
> > > > > > without flashing the file properties dialog.
> > > > > >
> > > > > > After showing the dialog .Value returns as expected. If I now
change
> > the
> > > > > > value in the linked cell A1, .Value returns the old value, need
to
> > see
> > > > the
> > > > > > dialog again for it to be updated.
> > > > > >
> > > > > > FWIW, after defining the custom property and not seeing the
dialog,
> > the
> > > > > > returned value appears to be 122 bytes of meaningless (to me)
> > numbers.
> > > > > >
> > > > > > dim arr() as byte
> > > > > > arr =
ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > > >
> > > > > > ' arr > 0 to 121
> > > > > >
> > > > > > Regards,
> > > > > > Peter T
> > > > > >
> > > > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in
message
> > > > > > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > > > > > Hi,
> > > > > > >
> > > > > > > When I create a CustomDocumentProperty programmatically and
set
> > the
> > > > > > > LinkToContent value to True, the value is not available to use
> > > > > > > programmatically before I have accessed the menu
File->Properties.
> > > > > > > To illustrate it I have included some sample code, just create
a
> > new
> > > > > > > workbook and add a button to it. Paste the following code into
the
> > > > > > > "ThisWorkbook"
> > > > > > >
> > > > > > > Private Sub Workbook_Open()
> > > > > > > ActiveSheet.Range("A1").Value = "Test"
> > > > > > > ActiveSheet.Names.Add Name:="TestName",
> > RefersTo:="=Sheet1!$A$1"
> > > > > > > ActiveWorkbook.CustomDocumentProperties.Add
> > Name:="CustomProp",
> > > > > > > LinkToContent:=True, Type:=msoPropertyTypeString,
> > > > LinkSource:="TestName"
> > > > > > > End Sub
> > > > > > >
> > > > > > > Add the following code into the button event handler
> > > > > > > MsgBox
ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > > > >
> > > > > > > Is there a way to get Excel to populate the
> > CustomDocumentProperties
> > > > > > > programmatically, We are integrating all our documents with
> > Meridio
> > > > which
> > > > > > is
> > > > > > > a EDRM solution and this program use the DocumentProperties
when
> > > > storing
> > > > > > the
> > > > > > > documents
> > > > > > >
> > > > > > > Regards
> > > > > > > Jan Petter
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi

Yes, I think this is how I have to solve it. It’s not as elegant as “Linked
to Content” properties but it works

Thanks for the help

"Peter T" wrote:

> As Nick says all appears to update when the file is saved. It also seems
> while not saved custom properties can only be read by vba methods or similar
> by automation (eg DSOFile dll doesn't seem to work with an open file).
>
> In which case, while the file is open can you simply update the value with
> LinkToContent:=False when the named cell value changes. Presumably this
> non-linked custom property can then be read immediately by Meridio.
>
> Regards,
> Peter T
>
>
> "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> news:32634AB9-88A2-4C1F-BBD1-5B91D185D5FC@microsoft.com...
> > Hi, thanks for taking the time to answer these question
> >
> > Unfortunately I have to have these Document Properties set before I can do
> a
> > "Save" The reason is that we are integrating excel with Meridio which is a
> > document archive system. Meridio has a plug-in in excel that captures the
> > "Save" and "Save-As" events and it is this plug-in that pulls out the
> > Document properties so it can use the as metadata in the Archive system.
> The
> > problem is that this happens before the Save happens in excel and
> therefore
> > all the properties show with no values in them
> >
> > Jan Petter
> >
> > "NickHK" wrote:
> >
> > > Jan,
> > > Seems that a "ActiveWorkbook.Save" flushes the value, so it can be
> correctly
> > > read. Office2K anyway.
> > >
> > > NickHK
> > >
> > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > news:A4516A2B-2E05-454C-ACD1-12D75C40D03A@microsoft.com...
> > > > Hi,
> > > >
> > > > Unfortunately the examples in the document do not help. I have changed
> my
> > > > code to reflect the examples and I have tried to use the "DS: OLE
> Document
> > > > Properties 2.0 Object Library" but with no luck
> > > >
> > > > Jan Petter
> > > >
> > > > "NickHK" wrote:
> > > >
> > > > > Jan,
> > > > > Forgot the link....
> > > > >
> > > > > Does this help ?
> > > > > http://www.cpearson.com/excel/docprop.htm
> > > > > NickHK
> > > > >
> > > > >
> > > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in message
> > > > > news:B786A2DC-D8BB-4C98-BB52-158D19D76FA2@microsoft.com...
> > > > > > This problem exists in the XL2003 SP2 version also.
> > > > > >
> > > > > > We have a lot of users that will use the excel spreadsheets and it
> > > can't
> > > > > bee
> > > > > > expected that they will access the File->properties menu before
> they
> > > save
> > > > > the
> > > > > > workbooks.
> > > > > > So if a solution or workaround for this problem exists I would
> really
> > > like
> > > > > > to hear about it.
> > > > > >
> > > > > > Jan Petter
> > > > > >
> > > > > >
> > > > > > "Peter T" wrote:
> > > > > >
> > > > > > > Hi Jon,
> > > > > > >
> > > > > > > I replicate what you describe in XL2000, can't see an obvious
> > > solution
> > > > > > > without flashing the file properties dialog.
> > > > > > >
> > > > > > > After showing the dialog .Value returns as expected. If I now
> change
> > > the
> > > > > > > value in the linked cell A1, .Value returns the old value, need
> to
> > > see
> > > > > the
> > > > > > > dialog again for it to be updated.
> > > > > > >
> > > > > > > FWIW, after defining the custom property and not seeing the
> dialog,
> > > the
> > > > > > > returned value appears to be 122 bytes of meaningless (to me)
> > > numbers.
> > > > > > >
> > > > > > > dim arr() as byte
> > > > > > > arr =
> ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > > > >
> > > > > > > ' arr > 0 to 121
> > > > > > >
> > > > > > > Regards,
> > > > > > > Peter T
> > > > > > >
> > > > > > > "Jan Petter" <JanPetter@discussions.microsoft.com> wrote in
> message
> > > > > > > news:F7489FC6-AE63-42D7-8224-A68AE272E368@microsoft.com...
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > When I create a CustomDocumentProperty programmatically and
> set
> > > the
> > > > > > > > LinkToContent value to True, the value is not available to use
> > > > > > > > programmatically before I have accessed the menu
> File->Properties.
> > > > > > > > To illustrate it I have included some sample code, just create
> a
> > > new
> > > > > > > > workbook and add a button to it. Paste the following code into
> the
> > > > > > > > "ThisWorkbook"
> > > > > > > >
> > > > > > > > Private Sub Workbook_Open()
> > > > > > > > ActiveSheet.Range("A1").Value = "Test"
> > > > > > > > ActiveSheet.Names.Add Name:="TestName",
> > > RefersTo:="=Sheet1!$A$1"
> > > > > > > > ActiveWorkbook.CustomDocumentProperties.Add
> > > Name:="CustomProp",
> > > > > > > > LinkToContent:=True, Type:=msoPropertyTypeString,
> > > > > LinkSource:="TestName"
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > Add the following code into the button event handler
> > > > > > > > MsgBox
> ActiveWorkbook.CustomDocumentProperties("CustomProp").Value
> > > > > > > >
> > > > > > > > Is there a way to get Excel to populate the
> > > CustomDocumentProperties
> > > > > > > > programmatically, We are integrating all our documents with
> > > Meridio
> > > > > which
> > > > > > > is
> > > > > > > > a EDRM solution and this program use the DocumentProperties
> when
> > > > > storing
> > > > > > > the
> > > > > > > > documents
> > > > > > > >
> > > > > > > > Regards
> > > > > > > > Jan Petter
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>