Linking Between Microsoft Project and Excel | Microsoft Office Project 2003 Inside Out

You can take the use of embedded objects one step further ”by linking them to their source. With a dynamic link between source and target, your embedded object can be updated whenever the source changes. The source and target information are essentially the same file, rather than just a separate copy, which is the case when they are not linked.

Creating a link between embedded objects is as easy as selecting a check box when you're embedding the object, whether it's a fragment you're inserting using the Paste Special dialog box or an entire file you're embedding using the Insert Object dialog box.

Be aware that when you link information between Microsoft Project and Excel, you always need to know the current location of the linked file and its update status. If the linked file is moved, the link is broken. If the information in the linked file becomes obsolete, the linked information also becomes out-of-date. However, when the conditions are right, linking is an excellent means of maintaining comprehensive and current information.

Linking from Excel to Project

In Microsoft Project, you can link an Excel worksheet fragment or chart in the chart area of a Gantt chart or the Objects box in the Task Form or Resource Form. To do this, follow the steps listed under "Embedding Copied Excel Information in a Project File" earlier in this chapter. In the Paste Special dialog box, select the Paste Link option.

You can link an entire existing Excel file in the chart area of a Gantt chart and the Notes tab in the Task Information, Resource Information, or Assignment Information dialog box. To do this, follow the steps listed under "Embedding an Entire Excel File in a Project File" earlier in this chapter. In the Insert Object dialog box, select the Link check box.

You can also copy worksheet cells and link them into Microsoft Project table cells . The data looks as if they were originally typed in Microsoft Project, but they're actually linked to Excel data. To link worksheet cell data in a Microsoft Project table, follow these steps:

Arrange the source Excel columns to match the target Microsoft Project field order and data type. Or, arrange the target columns in Microsoft Project to match the incoming Excel data.
In Excel, select the information to be linked in Microsoft Project and then click Copy on the Standard toolbar.
In Microsoft Project, display the view and click the cell that is to become the anchor cell for the linked information.

This anchor cell becomes the location of the upper-left cell of data selected in Excel.
Click Edit, Paste Special.
In the Paste Special dialog box, click the Paste Link option.
In the As box, click Text Data (see Figure 17-12).

Figure 17-12: Choose these options in the Paste Special dialog box to link worksheet data with project table data.

If you select Microsoft Excel Worksheet, a picture of the selected cells is embedded and linked, rather than flowing into the table cells as text.
Click OK.

The linked information appears at your selection point in your project. When changes are made to the information in the source application, those changes can be reflected in this project file.

Troubleshooting: You're getting paste error messages

Paste error messages are the result of a mismatch between the data type of the incoming Excel data and the Microsoft Project columns into which they're supposed to go. Click through the paste error messages to dismiss them.

Then review the order and type of the Excel data and the order and type of the Project sheet and see where the problem is. Make any necessary corrections, and repeat the Copy and Paste Special operation again.

Linking from Project to Excel

In Excel, you can link to an embedded Microsoft Project file. To do this, follow the steps listed under "Embedding an Existing Project File in Excel" earlier in this chapter. In the Insert Object dialog box, select the Link To File option.

You can also copy a set of table cells in Microsoft Project and link them into Excel worksheet cells. The data looks as if they were originally typed in Excel, but they're actually linked to Microsoft Project data. To link Microsoft Project table data to Excel worksheet cells, follow these steps:

In Microsoft Project, select the table data to be linked in Excel.
On the Standard toolbar, click Copy.
In Excel, click the cell that is to become the anchor cell for the linked information.

This anchor cell becomes the location of the upper-left cell of data selected in Microsoft Project.
Click Edit, Paste Special.
In the Paste Special dialog box, click the Paste Link option.
In the As box, click Text.

If you select Microsoft Project Document Object, a picture of the selected project data is embedded and linked, rather than flowing into the table cells as text (see Figure 17-13).

Figure 17-13: Selected project data inserted into Excel as a linked picture.
Click OK.

The linked information appears at the worksheet selection (see Figure 17-14). When changes are made to the data in the project plan, those changes are reflected in this linked data in Excel.

Figure 17-14: Selected project data inserted into Excel as linked sheet text.

Working with a Linked Object

After you have linked information in Microsoft Project or Excel, you can manipulate the information in certain ways. You can edit the source information and also accept updates in the target file from the source. You can review a list of all linked information in the file and then redirect or break the link if needed.

Editing Linked Information

If you try to change information in an embedded object, a message indicates that you cannot do so from within the target application without breaking the link, therefore protecting the linked information.

However, you can double-click a linked object to launch the source file in the source application so you can make the changes you want. This is different from working with an unlinked embedded object, with which you can work in the target application, using the temporarily changed menus and tools. With linked information, you always need to work in the source because the source always updates the target.

When finished updating the source, click Save and then return to the target application. Your changes are reflected there.

Updating Linked Objects

In both Microsoft Project and Excel, when you open the file that contains links, by default you will see a dialog box prompting you to update the file using the link (see Figure 17-15).

Figure 17-15: When you open a project plan or workbook that contains links, you'll see a message like this.

Click Yes to re-establish the link and update any information that has changed since the last time you opened and updated this file.

In Microsoft Project, if you do not want to see this alert each time you open the project containing links, click Tools, Options and then click the View tab. Clear the Show OLE Links Indicators check box.

In Excel, if you do not want to see this alert each time you open the workbook, click Tools, Options and then click the Edit tab. Clear the Ask To Update Automatic Links check box.

Viewing Links

In Microsoft Project, to review and work with links in your project, follow these steps:

Click Edit, Links.

The Links dialog box appears, showing all links existing in your project file.
If you chose not to update a link when opening the project file or if you turned the prompt off, you can select a link and click Update Now.
If the location of the linked workbook has changed, you can update the location information in the project by clicking Change Source.
To break the link with the source application, click Break Link.

When you break a link, the information remains in the project file as a separate embedded object. You can still view and edit the Excel information; it's simply no longer linked.

To review and work with links in Excel, follow these steps:

In Excel, open the workbook containing the links.
Click Edit, Links.

The Edit Links dialog box appears, showing all links existing in the workbook (see Figure 17-16).

Figure 17-16: Use the Edit Links dialog box in Excel to review and update the links in the current workbook.
If you chose not to update a link when opening the workbook or if you turned the prompt off, you can select a link and click Update Values.
If the location of the linked project has changed, update the location information in the workbook by clicking Change Source.
To break the link with the source application, click Break Link.

When you break a link, the project information remains in the workbook file as a separate embedded object. You can still view and edit the project information; it's simply no longer linked.