Certification Insider: Exchanging Data between Programs

Projects use people — and lots of ’em. And where you have people, you need communication. That means you, the anointed project manager, spend a lot of time gathering information from others and spitting it back out in a plethora of ways. Because you use other programs to manage projects in addition to Microsoft Project, you’re going to exchange data between programs. For Microsoft’s 70-632 exam, the most important thing to know is which tool is the best one for communicating information in a particular situation. But you also have to understand a few subtleties in Project’s importing and exporting tools.

Here’s a quick overview of how you can exchange information between Project and other programs:

• Copying and pasting is an easy way to get small amounts of information from one program to another. Copy task data from a Project table and paste it into Excel columns or a Word document or the other way around. You can copy and paste a picture of your Project schedule into another program, for example, a PowerPoint presentation for an executive review or an email to share a snapshot with team members.
• Linking data between files means that changes you make in one file appear automatically in the other. Linking is your best bet when you want to make sure you always see up-to-date information. A link is an ongoing connection between source and destination data. If you build a PowerPoint presentation for status meetings, inserting a Gantt chart view as a linked object will always show the current schedule. Double-clicking a linked object launches the source program and opens the source file so you end up editing the original file. Keep in mind that links point to the location of the source file. If you move the source file, the link breaks.
• Embedding is halfway between copying and linking. It inserts the object into the destination file with no links, so you won’t see changes made in the original file. Embedding data is great when you want to send the file to someone else and let that person edit it, for instance, when they don’t have access to your source files. Double-clicking an embedded object displays the commands from the source program, but you edit the embedded copy of the file. One caveat: embedded data inflates the size of the destination file.
• Importing means bringing data from another program into a Project file, such as cost estimates from an Excel spreadsheet or task names from a Word document. Unlike copying and pasting, you can control what data you import, where you import it, and how it squishes into the existing data.
• Exporting data from Project massages Project data into formats that other programs read. When the data gets to the other program, it acts like it was created there.

Copying Information

When another program stores information in columns and rows, it’s a snap to copy information from the program to Project — or the other direction. You select the cells you want to copy from the source file and paste them into cells in the destination file. For example, after you select cells to copy in Project, press Ctrl-C or choose Edit | Copy Cell to copy the selected cells to the Clipboard. In the destination file, click the top-left destination cell and use the appropriate paste command.

Copying data from another program into Project is a little trickier because Project won’t allow any old kind of data into its fields. Date fields want date values and duration fields want durations. However, you can solve this dilemma simply by rearranging the columns in the other program’s file so they appear in the same order as the columns in your Project table. Then, you can grab a whole swath of cells and paste them into Project with no complaints.

Creating Pretty Project Pictures

Project contains the Copy Picture to Office wizard, which can take pictures and plop them into Word documents, PowerPoint presentations, or Visio diagrams. If you want to put a Project picture into one of those files, go with the wizard. It snaps the pic, creates the destination file, and copies the picture into it. The only thing that’s difficult is remembering where the wizard is. It’s on the Analysis toolbar.

If the view you’re snapping shows the task outline, you can choose the number of outline levels to include in the picture. The wizard has a bunch of options for what to include in the picture, like the number of rows, how much of the timescale to include, and the picture dimensions.

When you just want a picture of your project, use the Copy Picture command. You get a picture in a lot fewer steps and can then paste into any file you want. In Project 2007, click Copy Picture, which looks like a camera, on the right end of the Standard toolbar. You can choose rows and timescale just like you can with the Copy Picture to Office Wizard.

Importing and Exporting Data

Importing and exporting data gives you a lot more control over how data moves between programs. You map fields in one program to fields in the other, so you can ensure that the data goes where you want the way you want.

Project even has wizards to help you import and export data to and from your Project files. If you open an Excel spreadsheet in Project, the Import Wizard starts up automatically. When you save a Project file to a format such as a text file, the Export Wizard launches.

The keys to import and export control are maps, which match Project fields up with fields or columns in another program. Project has several built-in maps that you can choose. You can modify an existing map or create one from scratch.

When you import data into Project, you have to get a couple of the settings just right — not only to import the data the way you want, but to pick the correct exam answers. Suppose you open an Excel file in Project and the Import Wizard starts up. The biggest hurdle is which option to choose for where to import the data.

• The “As a new project” option is easy. It creates a new Project file for the data.
• The “Append the data to the active project” option adds data to the end of your existing Project table, perfect when you’re importing several files into the same Project file.
• “Merge the data into the active project” is great when you want values to slide into cells in existing tasks, like when you import duration estimates into your Project file. When you merge data, you have to tell Project how to find the matching tasks. You tell Project which fields are unique identifiers for your tasks or resources. For example, a WBS code is perfect as a primary key for tasks. On the mapping page, you select the cell in the To column that represents the field you want to use as the primary key and click Set Merge Key. Both cells in the row change to MERGE KEY:<field name>, where field name is the name of the primary key field.

Exchanging Data with Excel

When you exchange data with Excel, you can import and export tasks, resources, and assignments all at once, each set of data going to its own Excel worksheet. Project also offers two Excel templates that have column headings already set up to map to Project fields.

If you want to export an entire project, the Export Wizard is short and sweet. After you tell the wizard what file to export to, select the Project Excel Template option and click Finish.

To export parts of your Project file to Excel, you use a map to specify the fields to export, just like you do with the Import Wizard. Because Excel can hold several worksheets, you can turn on checkboxes for Tasks, Resources, and Assignments, to export them to separate worksheets.

As long as you have both Project and Excel installed on your computer, Excel will have two templates designed to play well with Project’s Import Wizard:

• The Microsoft Project Task List Import Template has columns for ID, Name, Duration, Start, Deadline, Resource Names, and Notes.
• The Microsoft Project Plan Import Export Template contains the Task_Table, Resource_Table, and Assignment_Table worksheets so you can import task info, resource info, and resource assignment info into a Project file.

Think You Know Data Exchange? Test Yourself!

Each team lead submits status updates to you as Excel spreadsheets. You want to import these spreadsheets into Project and have the updated values go into cells for the matching tasks in your Project file.

How do you import data so the status updates copy into the correct tasks in Project?

Answer A: In the Import Wizard, select the “Append the data to the active project” option and set the merge key to the WBS field.

Answer B: In the Import Wizard, select the “Append the data to the active project” option and set the merge key to the Name field.

Answer C: In the Import Wizard, select the “Merge the data to the active project” option and set the merge key to the WBS field.

Answer D: In the Import Wizard, select the “Merge the data to the active project” option and set the merge key to the Name field.

No peeking! Scroll below the book ordering information to read the answer to this quiz.

Order the MCTS Self-Paced Training Kit (Exam 70-632): Managing Projects with Microsoft Office Project 2007.

To learn more about Microsoft certification, read, “Microsoft Project Management Certification: How to Get Started.”

The Answer to Test Yourself!

The field that you use for the merge key must be unique to each task.

Answer A is incorrect. The Append option adds the data to the end of the project table. It doesn’t merge updated status values into existing tasks.

Answer B is incorrect. The Append option adds the data to the end of the project table and the Name field isn’t necessarily unique to each task.

Answer D is incorrect. The Name field isn’t necessarily unique to each task.

Answer C is correct.