Using Document Properties

Each file you create in an Office application contains a set of document properties. The set is a combination of built-in document properties that Office defines—including Title, Author, Subject, and Comments—and custom document properties that you can define for the document. You conduct the setting and retrieval of built-in and custom document properties in the user interface through the Properties dialog box. Clicking Properties on the File menu in Word, Excel, or PowerPoint accesses the Properties dialog box. In Access, click Database Properties on the File menu.

NOTE
The object model associated with the document properties of a file is provided through the Microsoft Office 9.0 Object Library. You access the document properties through the BuiltInDocumentProperties property in Word, Excel, and PowerPoint, as the example below demonstrates. In Access, setting and returning document properties of a database is slightly more involved. You can set the built-in and custom properties by using the SummaryInfo and UserDefined Document object in the Documents collection. For more information, in Access ask the Assistant for help using the words "database properties."

Retrieve Built-In Document Properties

Suppose you want to build an index of all the documents kept on a local file server, cataloged by such document properties as Author, Title, Subject, and Comments. You retrieve these and other built-in document properties through the DocumentProperties object using the BuiltInDocumentProperties property of Word's Document object, Excel's Workbook object, and PowerPoint's Presentation object.

  1. Start Word. Open the Visual Basic Editor, insert a new module, and create a new procedure by typing Sub GetDocumentProperties.

  2. Add the following With…End block:

    3. With ActiveDocument.BuiltInDocumentProperties
End With

NOTE
If you're running this procedure in PowerPoint, you should change ActiveDocument to ActivePresentation. If you're running it in Excel, change it to ActiveWorkbook.

  1. In the With…End block created in step 2, type the following lines:

    2. Debug.Print "Author: " & .Item("Author").Value
Debug.Print "Subject: " & .Item("Subject").Value
Debug.Print "Title: " & .Item("Title").Value
Debug.Print "Comments: " & .Item("Comments").Value

    These properties are common to Word, Excel, and PowerPoint, and they always return a value. If any one isn't set, the value returns an empty string. The Debug.Print statement prints the specified string in the Immediate window, which you can display by clicking Immediate Window on the View menu in the Editor. The MsgBox function can serve the same purpose, but it displays the value in a message box that you have to close in order to continue working with the application or the Editor. The Debug.Print statement allows you to continue working without disruption.

  2. Just after the built-in properties specified above and before the End With statement, add the following lines:

    3. Debug.Print "Number of Words: " & _
    .Item("Number of Words").Value
Debug.Print "Number of Pages: " & _
    .Item("Number of Pages").Value

    Unlike in step 3, where the properties you added are common to Word, Excel, and PowerPoint, here few built-in document properties are specific to either Word or PowerPoint. These properties are listed in the Statistics tab in the Properties dialog box.

    Click to view at full size.

  3. If the Immediate Window isn't open, click Immediate Window on the View menu.

  4. Place the cursor in the GetDocumentProperties procedure and press F5 to run it. The specified built-in document properties for Word appear in the Immediate window.

How Retrieving Some Document Properties Results in an Error

Some properties, such as Author and Title, can only be returned and can't be set by the user. These properties are invalid until the first time they are set by the Office application. For example, the built-in document property Last Save Time isn't valid until the first time you save the document, workbook, or presentation. The Last Print Date built-in document property is also invalid until the first time you print the document, workbook, or presentation.

Debug.Print "Last Save Time: " & _
    .Item("Last Save Time").Value
Debug.Print "Last Print Date: " & _
    .Item("Last Print Date").Value

If you add the two lines above to the With…End block added in step 2 of the preceding example, an error would occur, and the valid values wouldn't be displayed in the Immediate window.

Set Document Properties

Because you don't usually set document properties first in a document, you can easily overlook them. Some companies require that every document that's sent electronically to customers has its document properties set. For example, you may want to explicitly set the built-in Company document property to be the same company-wide and set some comments in the built-in Comments document property. In the following example, document properties are set for the active document. However, in Chapter 5, document events such as Save and Close are used to prompt the user to set specific document properties before these actions are completed.

  1. Start the Visual Basic Editor in Word and click Module on the Insert menu.

  2. In the code module, add the following lines:

    3. Sub SetProperties
    With ActiveDocument.BuiltInDocumentProperties
        .Item("Company").Value = "My Company"
        .Item("Comments").Value = _
            "Please send mail to Dave@MyCompany.com"
    End With
End Sub

  3. Place the cursor in the procedure and press F5.

    4. The values for the built-in document properties Company and Comments are set.

NOTE
Because you're altering the document properties, this results in a state in which the document has been changed. Therefore, when you close the document and the document properties are changed, an alert is displayed asking whether you want to save the document. If you do not want this alert to be displayed, use code to explicitly save the document, using the Save method on the Document object after the With…End block.

  1. On the File menu, click Properties. The property information is added to the Company and Comments text boxes on the Summary tab.

Get Current User's Name

When you want to build an index of all the documents kept on a file server, cataloged by such document properties as Author, Title, Subject, and Comments, you may want to retrieve information such as the user's network logon name (if the user is using a machine on a computer network). This can allow you, for example, to build a change history table stored in a document or on a server. This information may be useful when you handle a document's Open, Save, or Close event, allowing you to automatically enter information into a document when the document is saved or closed. The next chapter discusses the use of document events, such as New, Open, Save, Close, and Print.

Using the GetUserName Windows API To retrieve the user's network logon name, you can use the Windows application programming interface (API) function GetUserName. The GetUserName Windows API returns the name of the user currently logged on to the system. If the user isn't logged onto the system, GetUserName returns an empty string. On a Windows NT 4 or Windows 2000 system, the user is required to log on before accessing the computer's file system; on a Windows 95 or Windows 98 system, however, the user is not required to log on. In the Visual Basic Editor of any Office application, insert a new standard code module and copy the following two functions along with the declaration, which must be typed at the top of the code module.

Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" ( _
        ByVal lpBuffer As String, _
        nSize As Long _
    ) As Long

Function CurrentUserName() As String
    Dim sBuffer As String * 25, lReturn As Long
    lReturn = GetUserName(sBuffer, 25)
    CurrentUserName = Left$(sBuffer, InStr(sBuffer, Chr(0)) - 1)
End Function

Sub GetCurrentUserName()
    Dim sUserName As String
    sUserName = CurrentUserName
    If Len(sUserName) Then
       Debug.Print sUserName
    Else
        Debug.Print "User not logged on."
    End If
End Sub

Place the cursor in the GetCurrentUserName procedure and press F5. The procedure assigns the string variable sUserName to the return value of the custom CurrentUserName function, defined above as the GetCurrentUserName procedure. In the custom CurrentUserName function, the procedure calls the Windows API GetUserName and removes any trailing spaces on the return string value. In the GetCurrentUserName procedure, if the length of the return value of the custom CurrentUserName function is more than zero, the user's name is printed to the Immediate window in the Visual Basic Editor. If the length of the string is zero, the Windows API GetUserName returns an empty string, indicating that the user isn't logged onto the computer system.