DocumentPrperties Collection

[Trent Argante]

RE: Excel 97
I found the following procedure in Help:
Sub AddCustomProperty(dp As DocumentProperties)
' This example adds a new custom document property and names it "Complete".
You must pass the custom _
DocumentProperties collection to the procedure.
dp.Add Name:="Complete", LinkToContent:=False, _
Type:=msoPropertyTypeBoolean, Value:=False
End Sub
I use Dim dp As DocumentProperties
then call the sub via AddCustomProperty dp
and it errors saying "Object variable or With block variable not set."
Various combinations of "Set dp = ..." result in various errors.

 

[Alok]

After Dim you need to set the document properties object
Set dp = ThisWorkbook.CustomDocumentProperties

Alok Joshi

 

[Max]

Hello: Is there a simple way to set the default user data, such as author,
title and company etc for all workbooks saved by me?? Thanks

 

[NickHK]

Max,
If that info is available to Excel, it will be filled in anyway.
Authour (UserName) is available under Tools>Options>General>Username

As for company name, if you did not fill that in during installation, there
probably a Reg key, maybe one of these:

HKEY_CURRENT_USER\Software\Microsoft\MS Setup (ACME)\User Info
Office 2000 installer:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserD
ata\S-1-5-18\Products\904000001E872D116BF00006799C897E\InstallProperties

You would need to find/correct the source of Excel's info, as according to
help:
BuiltinDocumentProperties Property
See Also Applies To Example Specifics
Returns a DocumentProperties collection that represents all the built-in
document properties for the specified workbook. Read-only.

Note the "Read-only", so you could cannot change this info on an existing
file from Excel.

There is the MS file DSOFile.exe which works with this type of OLE Storage.
Free download:
http://support.microsoft.com/kb/224351

IIRC, there is also an earlier version of the exe that works on earlier
versions of OLE storage.

NickHK

 

[owlnevada]

Setting the standard built in document properties is even easier than the
above. Just set them in the excel template that you start from for all your
excel files, word files, etc or use the following code:


strManager = "Document Control" 'or whatever you want to call it, etc
strCompany = "Division of Information Management"
strCategory = "Computerized Assignment Forms"
StrComment = "" 'other code sets this to a list of sheetnames in the
workbook that_ runs in a separarte procedure

ActiveWorkbook.BuiltinDocumentProperties("Title").Value = strTitle
ActiveWorkbook.BuiltinDocumentProperties("Subject").Value = strSubject
ActiveWorkbook.BuiltinDocumentProperties("Author").Value = strAuthor
ActiveWorkbook.BuiltinDocumentProperties("Manager").Value = strManager
ActiveWorkbook.BuiltinDocumentProperties("Company").Value = strCompany
ActiveWorkbook.BuiltinDocumentProperties("Category").Value = strCategory
ActiveWorkbook.BuiltinDocumentProperties("Comments").Value = strComment

These are just the ones I use but the other fields would be set in the same
manner. I have other code that sets the Comments field to the list of all the
sheetnames in the workbook so they can be viewed in the Explorer window when
those are turned on in the status bar.