File Properties Code

  • Thread starter Thread starter John Michl
  • Start date Start date
J

John Michl

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.
 
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.
 
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
 
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).
 
Back
Top