File Properties Code
 

How can I manipulate the File Properties for a Workbook. I tried recording
a macro following the keystrokes File | Properties and then entered data for
Title, Subject and Author but nothing appeared in the macro.

I'm trying to use the subject field to record a version number and date for
the model and transfer this exact data to a splash screen. My thought is
I'd entered the current version number and date in a cell which would be
transferred to the File | Properties when the workbook is saved and
transfers to the splash screen when the workbook is opened.

Thanks for the help.

File Properties Code
 

Look at document properties and builtindocument properties

See Chip Pearson's page on this topic:

http://www.cpearson.com/excel/docprop.htm

although not shown as a property in the object browser (or by intellisense),
this works



? activeworkbook.subject
This is the subject

Activeworkbook.Subject = "My new subject"
? activeworkbook.Subject
My new subject

Tested in Excel 2000.

--
Regards,
Tom Ogilvy

"John Michl" wrote in message
...
How can I manipulate the File Properties for a Workbook. I tried
recording
a macro following the keystrokes File | Properties and then entered data
for
Title, Subject and Author but nothing appeared in the macro.

I'm trying to use the subject field to record a version number and date
for
the model and transfer this exact data to a splash screen. My thought is
I'd entered the current version number and date in a cell which would be
transferred to the File | Properties when the workbook is saved and
transfers to the splash screen when the workbook is opened.

Thanks for the help.

File Properties Code
 

Awesome. Worked like a charm. Thank's Tom.

- John

"Tom Ogilvy" wrote in message
...
Look at document properties and builtindocument properties

See Chip Pearson's page on this topic:

http://www.cpearson.com/excel/docprop.htm

although not shown as a property in the object browser (or by
intellisense),
this works



? activeworkbook.subject
This is the subject

Activeworkbook.Subject = "My new subject"
? activeworkbook.Subject
My new subject

Tested in Excel 2000.

--
Regards,
Tom Ogilvy

"John Michl" wrote in message
...
How can I manipulate the File Properties for a Workbook. I tried
recording
a macro following the keystrokes File | Properties and then entered data
for
Title, Subject and Author but nothing appeared in the macro.

I'm trying to use the subject field to record a version number and date
for
the model and transfer this exact data to a splash screen. My thought
is
I'd entered the current version number and date in a cell which would be
transferred to the File | Properties when the workbook is saved and
transfers to the splash screen when the workbook is opened.

Thanks for the help.

File Properties Code
 

Spoke a little too soon. It works like a charm except for the "Company"
Property. The author updates fine but I receive a run time 438 - object
doesn't support this property" error on the Company statement. Is there a
different name for this proporty? I created a list of names using the code
in example 2. Thanks.


EXAMPLE 1
Private Sub Workbook_Open()

ActiveWorkbook.Author = "John A. Michl"
ActiveWorkbook.Company = "John Michl Consulting"

End Sub

EXAMPLE 2
Sub GetProps()
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 2).Value = p.Value
rw = rw + 1
Next
End Sub


"John Michl" wrote in message
...
Awesome. Worked like a charm. Thank's Tom.

- John

"Tom Ogilvy" wrote in message
...
Look at document properties and builtindocument properties

See Chip Pearson's page on this topic:

http://www.cpearson.com/excel/docprop.htm

although not shown as a property in the object browser (or by
intellisense),
this works



? activeworkbook.subject
This is the subject

Activeworkbook.Subject = "My new subject"
? activeworkbook.Subject
My new subject

Tested in Excel 2000.

--
Regards,
Tom Ogilvy

"John Michl" wrote in message
...
How can I manipulate the File Properties for a Workbook. I tried
recording
a macro following the keystrokes File | Properties and then entered
data
for
Title, Subject and Author but nothing appeared in the macro.

I'm trying to use the subject field to record a version number and
date
for
the model and transfer this exact data to a splash screen. My thought
is
I'd entered the current version number and date in a cell which would
be
transferred to the File | Properties when the workbook is saved and
transfers to the splash screen when the workbook is opened.

Thanks for the help.

File Properties Code
 

Activeworkbook.BuiltinDocumentProperties("Company" ).Value


Private Sub Workbook_Open()

ActiveWorkbook.Author = "John A. Michl"
ActiveWorkbook.BuiltinDocumentProperties("Company" ).Value _
= "John Michl Consulting"
End Sub


It looks like it only supports Title, Author and Subject directly (I didn't
do extensive testing).

--
Regards,
Tom Ogilvy


"John Michl" wrote in message
...
Spoke a little too soon. It works like a charm except for the "Company"
Property. The author updates fine but I receive a run time 438 - object
doesn't support this property" error on the Company statement. Is there a
different name for this proporty? I created a list of names using the
code
in example 2. Thanks.


EXAMPLE 1
Private Sub Workbook_Open()

ActiveWorkbook.Author = "John A. Michl"
ActiveWorkbook.Company = "John Michl Consulting"

End Sub

EXAMPLE 2
Sub GetProps()
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 2).Value = p.Value
rw = rw + 1
Next
End Sub


"John Michl" wrote in message
...
Awesome. Worked like a charm. Thank's Tom.

- John

"Tom Ogilvy" wrote in message
...
Look at document properties and builtindocument properties

See Chip Pearson's page on this topic:

http://www.cpearson.com/excel/docprop.htm

although not shown as a property in the object browser (or by
intellisense),
this works



? activeworkbook.subject
This is the subject

Activeworkbook.Subject = "My new subject"
? activeworkbook.Subject
My new subject

Tested in Excel 2000.

--
Regards,
Tom Ogilvy

"John Michl" wrote in message
...
How can I manipulate the File Properties for a Workbook. I tried
recording
a macro following the keystrokes File | Properties and then entered
data
for
Title, Subject and Author but nothing appeared in the macro.

I'm trying to use the subject field to record a version number and
date
for
the model and transfer this exact data to a splash screen. My
thought
is
I'd entered the current version number and date in a cell which
would
be
transferred to the File | Properties when the workbook is saved and
transfers to the splash screen when the workbook is opened.

Thanks for the help.