link comments section of properties dialog box to footer in Excel
 

Hi

I use the Comments section in Summary of File Properties quite a lot and
would like to link the contents into the file footer.

I managed this when using Microsoft Word by inserting the field code
{COMMENTS} into the footer then creating a macro to update it on file
closure. Sadly I have not been able to do this with Excel.

I can reference a cell in the footer but cant seem to link the comments
section to a cell - can anyone help?

Regards
Kenny W
XP Pro and Office 2003

link comments section of properties dialog box to footer in Excel
 

Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
ActiveSheet.PageSetup.CenterHeader = s
End Sub
--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Hi

I use the Comments section in Summary of File Properties quite a lot and
would like to link the contents into the file footer.

I managed this when using Microsoft Word by inserting the field code
{COMMENTS} into the footer then creating a macro to update it on file
closure. Sadly I have not been able to do this with Excel.

I can reference a cell in the footer but cant seem to link the comments
section to a cell - can anyone help?

Regards
Kenny W
XP Pro and Office 2003

link comments section of properties dialog box to footer in Excel
 

Thanks Gary that worked a treat.

For future reference is it possible to put the value into a cell?

Regards
Kenny

"Gary''s Student" wrote in message
...
Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
ActiveSheet.PageSetup.CenterHeader = s
End Sub
--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Hi

I use the Comments section in Summary of File Properties quite a lot and
would like to link the contents into the file footer.

I managed this when using Microsoft Word by inserting the field code
{COMMENTS} into the footer then creating a macro to update it on file
closure. Sadly I have not been able to do this with Excel.

I can reference a cell in the footer but cant seem to link the comments
section to a cell - can anyone help?

Regards
Kenny W
XP Pro and Office 2003

link comments section of properties dialog box to footer in Ex
 

Su

Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
Range("A1").Value = s
End Sub

--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Thanks Gary that worked a treat.

For future reference is it possible to put the value into a cell?

Regards
Kenny

"Gary''s Student" wrote in message
...
Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
ActiveSheet.PageSetup.CenterHeader = s
End Sub
--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Hi

I use the Comments section in Summary of File Properties quite a lot and
would like to link the contents into the file footer.

I managed this when using Microsoft Word by inserting the field code
{COMMENTS} into the footer then creating a macro to update it on file
closure. Sadly I have not been able to do this with Excel.

I can reference a cell in the footer but cant seem to link the comments
section to a cell - can anyone help?

Regards
Kenny W
XP Pro and Office 2003

link comments section of properties dialog box to footer in Ex
 

Many,many thanks,

Worked perfectly!


"Gary''s Student" wrote in message
...
Su

Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
Range("A1").Value = s
End Sub

--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Thanks Gary that worked a treat.

For future reference is it possible to put the value into a cell?

Regards
Kenny

"Gary''s Student" wrote in
message
...
Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
ActiveSheet.PageSetup.CenterHeader = s
End Sub
--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Hi

I use the Comments section in Summary of File Properties quite a lot
and
would like to link the contents into the file footer.

I managed this when using Microsoft Word by inserting the field code
{COMMENTS} into the footer then creating a macro to update it on file
closure. Sadly I have not been able to do this with Excel.

I can reference a cell in the footer but cant seem to link the
comments
section to a cell - can anyone help?

Regards
Kenny W
XP Pro and Office 2003

link comments section of properties dialog box to footer in Excel
 

Add this UDF to your workbook.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

In a cell enter =docprops("Comments")


Gord Dibben MS Excel MVP


On Fri, 2 Jan 2009 14:27:34 -0000, "Forum freak \(at work\)"
wrote:

Thanks Gary that worked a treat.

For future reference is it possible to put the value into a cell?

Regards
Kenny

"Gary''s Student" wrote in message
...
Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
ActiveSheet.PageSetup.CenterHeader = s
End Sub
--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Hi

I use the Comments section in Summary of File Properties quite a lot and
would like to link the contents into the file footer.

I managed this when using Microsoft Word by inserting the field code
{COMMENTS} into the footer then creating a macro to update it on file
closure. Sadly I have not been able to do this with Excel.

I can reference a cell in the footer but cant seem to link the comments
section to a cell - can anyone help?

Regards
Kenny W
XP Pro and Office 2003

link comments section of properties dialog box to footer in Excel
 

In addition, run this macro get a complete list on a new sheet.

Sub documentprops()
'list of properties on a new sheet
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 4).Value = "=DocProps(" & "A" & rw & ")"
rw = rw + 1
Next
End Sub

Or this one to get a list of Custom Properties if you have any.

Sub customprops()
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.CustomDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 4).Value = p.Value
rw = rw + 1
Next
End Sub


Gord


On Fri, 02 Jan 2009 09:03:26 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Add this UDF to your workbook.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

In a cell enter =docprops("Comments")


Gord Dibben MS Excel MVP


On Fri, 2 Jan 2009 14:27:34 -0000, "Forum freak \(at work\)"
wrote:

Thanks Gary that worked a treat.

For future reference is it possible to put the value into a cell?

Regards
Kenny

"Gary''s Student" wrote in message
...
Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue
ActiveSheet.PageSetup.CenterHeader = s
End Sub
--
Gary''s Student - gsnu2007k


"Forum freak (at work)" wrote:

Hi

I use the Comments section in Summary of File Properties quite a lot and
would like to link the contents into the file footer.

I managed this when using Microsoft Word by inserting the field code
{COMMENTS} into the footer then creating a macro to update it on file
closure. Sadly I have not been able to do this with Excel.

I can reference a cell in the footer but cant seem to link the comments
section to a cell - can anyone help?

Regards
Kenny W
XP Pro and Office 2003