Linking Selected Data Between Applications

The copy operations described in the preceding section produce static copies in the destination document; the pasted copy of the data does not change if the source data changes. However, you can also paste a value from another application that is a linked reference to the data location in the source document. The linked reference can be updated to reflect changes in the value that is stored in the source document.

For information about using external dependency links, see "Creating Links Between Tasks in Separate Projects," p. 618 .

Linking Microsoft Project Data Fields to External Sources

You can paste a link to an external data source into a Microsoft Project table. The external source can be another application (such as an Excel or Word file) or another Project document. For example, an Excel workbook could serve as the source for resource names and cost rates in a Project document. You could also link the constraint date for a task to a date in another file if you wanted the task to start or finish on that date.

To link Microsoft Project field values to values stored in other sources, follow these steps:

1. Select the source data (for example, a cell or range of cells in an Excel worksheet). You can select a single value or several values. If you select several values, be sure that the order of the values matches the order of the values in the table that you view when you paste the values into Microsoft Project.

2. Copy the data to the Clipboard.

3. Activate Microsoft Project and select a view that has a table with the columns arranged to match the order of the data you are copying.

4. Select the row in the table that is to receive the data.

5. Choose Edit, Paste Special. The Paste Special dialog box appears (see Figure 18.5).

   Figure 18.5. The Paste Special dialog box lets you specify the format for the data that will be pasted into the receiving file.

   

6. Select the Paste Link option.

   TIP

   Choose the Paste Link option button before you select the format of the pasted data in the As box. Changing the link option can change the options in the As box.

7. In the As box, choose Text Data as the type of link if you want the data to become text in a table. Project attempts to convert text data into numeric data in a number field or into a date in a date field.

   NOTE

   If you are in a Gantt Chart view, the Paste Special dialog box contains more options in the As box than if you are in the Resource Sheet view (as shown by the graphics options in Figure 18.5). You can choose the Worksheet or Picture options if you want to paste the data as a picture object in a graphic area. (See "Placing Objects into Microsoft Project," later in this chapter.) In the Gantt Chart view, for example, both those options would create a graphic object in the bar chart area of the view.

8. Click OK to establish the link. By default Project displays a small triangle in the lower-right corner of each cell that is linked to a source for its data. You can see the link indicators for the Name , the Standard Rate, and the Overtime Rate values in Figure 18.6. The resource names and their accompanying cost rate information are dynamically linked to the Excel worksheet in which the resource information is maintained .

   Figure 18.6. Linked cells in a table view display a link indicator, a small triangle in the lower-right corner of the cell.

   

TIP

Double-clicking a resource cell normally displays the Resource Information dialog box. Double-clicking a cell that is linked to an external source, however, opens the external source so that you can view or edit the source data. If you want to see the Information dialog box for the task, resource, or assignment that contains the linked cell, click the Information button in the Standard toolbar.

TIP

If you don't see the link indicator where it should be displayed, choose Tools, Options and select the View tab in the Options dialog box. Make sure that the OLE Links Indicators check box is selected.

If you attempt to paste a link with mismatched data, you receive an object linking and embedding (OLE) error message which states that the operation cannot be completed. Unlike with the regular Paste command, if there is a data mismatch while pasting a block of values, Project halts the operation and removes all values that were pasted in during that operation. These values are removed because a block of cells is considered one link. If one cell contains a mismatch, the entire paste link is ignored.

Refreshing Linked Data in Microsoft Project

If you save a project file that contains linked values, Project saves the current values of the linked fields, along with the reference to the source for the value. That way, when you open a file with linked values, Project can display the most recent values.

When you open a Project file that contains links to other files, Project asks if you want to reestablish the links. If you do, Project can refresh the values in the linked cells with the current values in the source files (see Figure 18.7). If you select Yes, Project retrieves the current or saved values of each link's source. If you select No, Project opens the Project document and displays the last saved values for the linked cells. You can update the links later.

Figure 18.7. When opening a Project file with links to other files, you can have Project refresh the links or use the last values that were saved in the Project file.

You can update the linked values in a Project document at any time by using the Edit, Links command. The source application does not need to be open in order for the linked values to be refreshed. The Links dialog box lists all the external links in the current document (see Figure 18.8).

Figure 18.8. All external sources of linked data are identified in the Links dialog box.

The Links list usually displays the path to the source, the document type of the source file, and the update status of each link. These three items are displayed in greater detail at the bottom of the dialog box for the selected link. If the filename and link reference are too long, the path to the source is truncated.

In Figure 18.8, the selected item in the Links list shows the path to the file truncated (because it's too long for the display), but the details below show the filename ( New Employees Pay Rates.xls ) and the location of the linked data within the file to be in the sheet named Sheet1 , the cell range R3C2:R12C3 (or B3:C12 , in standard Excel range notation). The type is identified as Microsoft Excel Worksheet. The update status is Manual, which means that you must click the Update Now button to refresh the values in Microsoft Project.

If the update status is Automatic, and if the file is open in memory and supports automatic updating, changes in the source appear immediately in the Project document while it is also open in memory. Even if you select the Automatic update choice, some source applications don't support automatic updates, and the Update column at the top of the dialog box displays Unavail, as shown with the link to the Terry Uland skills assessment.doc source file shown in Figure 18.8. The update status is also shown as Unavail when the Project document has not been updated during the current editing session.

TIP

If an object's Update field at the bottom of the dialog box has the Automatic button selected and you want to select the Manual button, do not make the change if the update status for the link in the list of links is unavailable. You should update the link with the Update Now button before you change the update method.

NOTE

Notice in Figure 18.8 that there is just one link reference in the Links dialog box for the whole range of Excel cells that was pasted in the link operation. If you need to maintain each of the cells as separate links, you should copy and paste each of the cells individually.

To work with the links to external sources, follow these steps:

1. Choose Edit, Links from the menu to display the Links dialog box (refer to Figure 18.8).

   TIP

   If the Links command is dimmed, then the document has no linked values.

2. Select all the links that you want to refresh.

3. Click the Update Now button to refresh all the data links you have selected. The source for each link that you select is searched for the current values.

4. If you want to open the application document named in the selected link reference, click the Open Source button. You cannot select multiple links and click the Open Source button. You can have multiple linked source documents open simultaneously ; however, you must open each of them individually. Other options in the Links dialog box include the following:

   • If you want to remove the selected link, click the Break Link button. The current value remains in the link location, but the reference to an external source disappears.

   • If you want to change the source of a selected link, click the Change Source button, and the Change Source dialog box appears (see Figure 18.9). Click the Browse button and choose another file to link to.

     Figure 18.9. You can use the Change Source dialog box to redefine the link source.

     

TIP

Although you can browse through the directory of files to find the filename of a new source to link to, you might also need to indicate the location within the new source file to complete the change. For that reason, it is usually best to paste new links over the old ones instead of using this dialog box.

Deleting Links to External Sources

As pointed out in step 4 in the preceding section, if you break the link to an external source, Project retains the current value that was linked but disassociates the value with the external source. Similarly, if you attempt to type over a field value that is linked to an external source, you are warned that the link will be lost, and you are offered the opportunity to proceed or to cancel the data entry. Choose No to abandon the editing change and preserve the link. If you choose Yes (to proceed with the change), the dynamic data exchange (DDE) link reference is lost. Fortunately, you can undo the change by using the Edit, Undo command.

To delete the data and its link to an external source, you can select the field whose link you want to remove and choose Edit, Clear, Contents (or press Ctrl+Delete). You are then asked to confirm the deletion.

CAUTION

If you delete the link in a cell that is part of a block of linked values, the link for all cells in the block is removednot just the link for the one cell. You can use the Edit, Undo command to restore the links.

Identifying Tasks or Resources with Links Attached

You can filter task or resource tables to determine which tasks or resources use linked data from other sources. For either a task view or a resource view, you can choose the Linked Fields filter from the Filters drop-down list. To find the linked values, search for the links indicator in the lower-right corner of the cells.

Pasting Links to Microsoft Project Data into Other Applications

Both Microsoft Excel and Microsoft Word accept pasted links to individual data cells in Microsoft Project tables. If you want to copy a single linked value to one of these applications, you only have to display a table that has a cell for the value you want to use, select the cell, and use the Copy command. Then, in Word or Excel, you use the Edit, Paste Special command and use the Paste Link option to paste the data as text. If you want to copy a block of values, you should modify a table in Project so that the values you want to copy are adjacent to each other. Select the block and, as with a single value, use the Edit, Paste Special and Paste Link commands to paste the block of values in Word or Excel. The pasted block of data is a single entity in both applications, and when you update the links in the other application, all the values in the block are updated.

NOTE

There are significant formatting issues related to displaying the Project data in other applications. For example, duration fields may display the duration in minutes, multiplied by 10 (an hour appears as 600), and work fields display the work as minutes, multiplied by 1,000 (an hour appears as 60,000). The durations may also be considered as text by the application as the unit can be copied with the value. The data has to be processed in order to provide a meaningful display for users.