You can integrate information from Microsoft Project and Excel by embedding a source file from one application as an embedded object in a file in the target application. When you embed an object in an application, you’re basically inserting an entire file, along with all of its source application’s capabilities, into the target application file.
Therefore, not only can you have a picture of a Gantt chart in an Excel worksheet, but you can double-click that picture of the Gantt chart to access Microsoft Project commands and change the Gantt chart data. You can even switch to a different Microsoft Project view, such as the Resource Sheet, and change information there as well. Likewise, in Microsoft Project, you can have a picture of an S-curve graph generated by Excel and also double-click it to launch Excel and edit the format of the graph or even the underlying data for the graph.
Whether you insert all or part of an Excel worksheet or chart into Microsoft Project as an embedded object, when you double-click the object, you can access the entire Excel workbook. The benefit of inserting a portion of an Excel worksheet or chart is that the portion you select is what appears in the embedded object. If you insert an entire file, you can drag its edges to change the portion of the embedded object that’s visible.
To copy and paste selected Excel information as an embedded object, follow these steps:
In Excel, select the data or chart you want to insert as an embedded object in Microsoft Project.
On the Home tab of the Ribbon, in the Clipboard group, click Copy.
In your Microsoft Project file, display the location where you want to place the object.
Only the chart area of a Gantt chart and the Objects box in the Task Form or Resource Form can accept a chart or worksheet fragment as an embedded object.
Click Edit, Paste Special.
In the Paste Special dialog box, select the Paste option.
In the As box, click Microsoft Excel Worksheet or Microsoft Excel Chart.
Click OK.
The data or chart is embedded in Microsoft Project.
To embed an entire existing Excel file into a Microsoft Project file, follow these steps:
In Microsoft Project, open the project plan in which you want to embed the Excel file.
Display the location at which you want to insert the file.
Only the chart area of a Gantt chart, the Objects box in the Task Form or Resource Form, and the Notes tab in the Task Information, Resource Information, or Assignment Information dialog box can accept an Excel file as an embedded object.
Click Insert, Object.
To insert an object onto the Notes tab of one of the Information dialog boxes, click the Insert Object button above the Notes area in the dialog box. (You can’t click Insert, Object while the dialog box is open.)
In the Insert Object dialog box, select the Create From File option.
If you want the embedded Excel file to be linked to the source, select the Link check box.
If you link the file, any changes to the file in Excel update the embedded object in Microsoft Project. If you try to change the embedded object, you’ll see a message indicating that the object is linked, and if you change information, the link will be removed. In this way, the linked information is protected.
If you don’t link the object, you’re essentially making a copy of the original object, and it becomes a separate entity from the original. You can change information in the embedded version without affecting the source data.
If you want the embedded object to be displayed as an Excel icon in your project, rather than showing as part of the worksheet, select the Display As Icon check box.
Click the Browse button and find the location of the Excel file you want to insert. Double-click the file.
The file’s name appears in the File Name box in the Insert Object dialog box.
Click Insert.
In the Insert Object dialog box, click OK.
The Excel file appears in the area you selected. Drag to move or resize the object as needed (Figure 17-6).
Note
You can embed an Excel file in a note associated with the project as a whole. First, display the project summary task by clicking Tools, Options, View and then selecting the Show Project Summary Task check box. Click OK.
Double-click the project summary task. In the Summary Task Information dialog box, click the Notes tab. Click the Insert Object button and add the Excel file.
You can create a new Excel workbook or chart as an embedded object in your project. In this case, the new Excel object exists only within your project file.
Note
If you think you would ever want to work on the Excel file outside of Microsoft Project, you should instead create the file in Excel and embed or link it within Microsoft Project.
To create an embedded Excel file within Microsoft Project, follow these steps:
In your project plan, display the location at which you want to embed the object.
Only the chart area of a Gantt chart, the Objects box in the Task Form or Resource Form, and the Notes tab in the Task Information, Resource Information, or Assignment Information dialog box can accept an Excel file as an embedded object.
Click Insert, Object.
To insert an object into the Notes tab of one of the Information dialog boxes, click the Insert Object button above the Notes area in the dialog box. (You can’t click Insert, Object while the dialog box is open.)
In the Insert Object dialog box, select the Create New option.
In the Object Type box, click Microsoft Excel Chart or Microsoft Excel Worksheet.
If you want the new object to be displayed as an icon in the project, select the Display As Icon check box.
Click OK.
An Excel worksheet or chart appears in the location you selected. Double-click the Excel object to start adding your information.
To open and work with an embedded Excel object, simply double-click the object. Excel launches and opens the embedded Excel object. When finished editing the Excel object, click the Microsoft Office Button and then click Exit Excel. The Excel window closes, and the modified Excel object appears in Microsoft Project.
You can embed a new or existing Microsoft Project file as an object in an Excel worksheet. When you double-click the Microsoft Project object in Excel, Microsoft Project menus and commands temporarily replace those of Excel so that you can create or edit project information without leaving the Excel file.
To embed an existing Microsoft Project file in Excel, follow these steps:
Open the Excel file in which you want to embed the existing Microsoft Project file.
Select the location at which you want the Microsoft Project object to be embedded.
On the Insert tab of the Ribbon, in the Text group, click Object.
In the Object dialog box, click the Create From File tab.
Click the Browse button to open the Browse window. Navigate through your computer’s filing system (and onto your network if applicable) to find the drive and folder where the project file is located.
Double-click the file.
The file’s path and name appear in the File Name box in the Object dialog box.
If you want the embedded Microsoft Project object to be linked to the source file, select the Link check box.
If you link the object, any changes in the source project plan update the embedded object. If you try to change the embedded object, you’ll see a message indicating that the object is linked, and you cannot change the information. This protects the integrity of the source information.
If you don’t link the object, you’re making a copy of the original object, which becomes a separate entity from the original. You can change the embedded project without affecting information in the original project.
If you want the embedded project to be displayed as a Microsoft Project icon in Excel, rather than showing a part of the Gantt Chart or other view, select the Display As Icon check box.
Click OK.
Part of a view of the selected project file appears in the location you selected (see Figure 17-7).
If you selected the Display As Icon check box, the embedded project file appears as the Microsoft Project icon (by default) in the selected location. This is particularly useful when empty areas on your Excel worksheet are hard to find.
Although you can create a new Microsoft Project file as an embedded object in Excel, you should avoid doing so because the Microsoft Project object would exist only within the Excel file. Opening Excel to access a Microsoft Project object would quickly grow tiresome as you work with your schedule throughout the life of the project. However, if you do decide to create a new Microsoft Project file within Excel, follow these steps:
Open the Excel file in which you want to create a new Microsoft Project file as an embedded object.
Select the location at which you want the new Microsoft Project object to be embedded.
On the Insert tab of the Ribbon, in the Text group, click Object.
In the Object dialog box, click the Create New tab, if necessary.
In the Object Type box, click Microsoft Office Project Document (see Figure 17-8).
If you want the embedded project to be displayed as an icon in Excel, rather than showing a part of the Gantt Chart or other view, select the Display As Icon check box.
Click OK.
Part of a view of the new project file appears.
If you selected the Display As Icon check box, the embedded project file appears as the Microsoft Project icon (by default) in the selected location.
Double-click the new project to add information to it on the spot.
Whenever you want to work with the embedded project file, just double-click the object. The Excel menus change to reflect the relevant Microsoft Project menus (see Figure 17-9). You can use Microsoft Project in the usual way: switch views, apply tables and filters, change data, run calculations, and so on.
