Bringing Excel workbook information into Project

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Want to start your project in Excel? You can use the Excel template and then easily transfer the information from Microsoft Excel workbooks to Microsoft Project files. The Excel template automatically installs to the Office template folder (drive :\ Program Files\Microsoft 0ffice\Templates\1033) and is available in the Templates dialog box in Excel.

Note The Excel template made its appearance in Project 2002.

Note You can use any of the existing import/export maps to either import to or export from an Excel workbook, but you can't import an Excel PivotTable into Project.

Follow these steps to transfer Excel information to Project:

1. Open Excel, and choose FileONew.

2. From the New Workbook task pane, click General Templates. Excel displays the Templates dialog box, as shown in Figure 26-21.

Figure 26-21: You find the Excel template on the Spreadsheet Solutions tab of the Templates dialog box.

3. Click the Spreadsheet Solutions tab.

4. Choose Microsoft Project Task List Import Template. The workbook that you see looks like the one shown in Figure 26-22.

Biuai

E'le El! ¡t View insert Fnrmat loo Is Bala yainrfow Help

□ sBd« #ay * «- »-1az-si|a«»»* - ts>, GS.

Qtaia^^Sa v) tl r^Roply with Changes... End Review... , Artal ,10- BIV 513i It % , 'Jii ff'i

E'le El! ¡t View insert Fnrmat loo Is Bala yainrfow Help

□ sBd« #ay * «- »-1az-si|a«»»* - ts>, GS.

Qtaia^^Sa v) tl r^Roply with Changes... End Review... , Artal ,10- BIV 513i It % , 'Jii ff'i

A

B

c

D

Deadline

F

1

ID

Name

Duration

Start

Resource Names

Notes

2

3

4

5

È

7

S

9

10

11

12

13

14

15

15

1?

13

19

20

21

22

Figure 26-22: An Excel workbook that's based on the Microsoft Project Task List Import Template.

Note The first time that you use the template, you may be prompted for your Project CD *""""' so that the template can be installed.

If you already started your project in Excel without using the template, you can still use the Import Wizard to bring your Excel data into Project. Follow these steps to do so:

1. In Project, choose FileOOpen or click the Open button on the Standard toolbar.

2. Open the Files of type list box, and select Microsoft Excel Workbooks, as shown in Figure 26-23.

Open

Look in:

i_J Project Z003 Bitte Ddta Files

¡2 1 «a >' Li ms

My Recent Documents

^Budget. xls ■^Conference costs .xk PivotTable.xls

Desktop

My Documents

My Computer

My Network Pieces

File name:

3 oçpc...

Files of type: [Microstf t Excel Wcrkbooks (• .xls)

▼j 1 cancel

Figure 26-23: Set the type of file that you want to import to Microsoft Excel Workbooks.

3. Use the Look in list box to navigate to the folder that contains the Excel workbook that you want to import.

4. Highlight the workbook, and click Open. Project starts the Import Wizard.

5. Click Next. In the Map dialog box, choose New map.

Note |f you choose the Use existing map option. Project displays the list of maps from

*""""' which you can choose. After you select a map. Project imports your data.

6. Click Next. In the Import Mode dialog box, select the As a new project option button.

Note |f you select the Append the data to the active project option button. Project imports the data and places it after any existing tasks in the Project file. If you select the Merge the data into the active project option button, you'll complete Steps 7 and 8, but in the Task Mapping dialog box, you'll click the Set Merge Key button to identify the field that's found in both the Excel file and the Project file.

7. Click Next. In the Map Options dialog box, select the types of data that you want to import, as shown in Figure 26-24.

Figure 26-24: Select the types of data that you want to import.

Cross-Reference

^Note

8. Click Next. If necessary, make changes to the Task Mapping dialog box, as shown in Figure 26-25. Select the Source worksheet name; Project attempts to resolve the column names in the worksheet with Project fields. Look for fields that have "(not mapped)" in the To: Microsoft Office Project Field. Type in the Project field name or select it from the list that appears when you click in the To: Microsoft Office Project Field. You should also verify the mapping of all fields the first time you use a new Map. If you used Name in Excel as the column title, you will get unexpected results, because Project uses Resource Name and Task Name, but not Name.

For a complete list of the functions of each field in this dialog box, see the section "Sending Project data to Excel," earlier in this chapter.

9. Click Next. You see the final box of the Export Wizard, in which you can elect to save the map that you just defined.

If you selected the Resources and the Assignments check boxes as well as the Tasks check box in the Map Options dialog box (shown previously in Figure 26-20), when you click Next, the Export Wizard displays additional boxes that are almost identical to the Task Mapping dialog box, but for each of these data types.

10. If you elect to save your map by clicking the Save Map button, Project displays the Save Map dialog box. Provide a name for the new map in the Map name text box.

Import Wizard - Task Mapping

Map Tasks Data

Source worksheet name:

Mi verify or edit Project's assumptions fa how you want to map the data,

Source worksheet name:

verify or edit Project's assumptions fa how you want to map the data,

1 ID

From: Excel Field

To: Mcrosoft Offne Protect Field

Data Type !

TEXT

Name

;Name

TEXT

BCW5

:ecws

TEXT

ECWP

;BCWP

TEXT

ACWP

: AC'WP

TEXT v

Excel :

[D

Name

BCW5

BCWP

ACWP

MS Project:

ID

Name

sews

BCWP

ACWP

1

Conference

i $7,057.50

i $920.00

I $1,560.00

Preview:

2

Preplanning

! $920.00

i $920.00

i $1,560.00

3

initial planning

1 $920.00

I tlsa > I |r finBlj"""'! cancet |

tjelp

I tlsa > I |r finBlj"""'! cancet |

Figure 26-25: The Task Mapping dialog box shows the fields that Project expects to import from your workbook.

Bringing Access database information into Project

Importing Access databases into Project is similar to importing Excel workbooks, except that you can import all or part of an Access database into a Project file. Again, you use an import/export map to describe to Project the type of data that you're importing. If the map that you need doesn't exist, you must create it. Refer to the section "Sending Project data to an Access database," earlier in this chapter, for details about creating maps.

To import some or all of an Access database into Project, follow these steps:

1. Choose FileOOpen, or click the Open button on the Standard toolbar.

2. Open the Files of type list box, and select Microsoft Access Databases.

3. Use the Look in list box to navigate to the folder that contains the Access database that you want to import.

4. Highlight the database, and click Open. Project starts the Import Wizard.

5. Click Next. In the Map dialog box, choose New map.

Note If you choose the Use existing map option. Project displays a list of available maps from which you can choose. After you select a map. Project imports the Access file.

6. Click Next. In the Import Mode dialog box, select the As a new project option button.

If you select the Append the data to the active project option button. Project imports the data and places it after any existing tasks in the Project file. If you select the Merge the data into the active project option button, you'll complete Steps 7 and 8, and in the Task Mapping dialog box, you'll click the Set Merge Key button to identify the field that's found in both the Excel file and the Project file.

7. Click Next. In the Map Options dialog box, select the types of data that you want to import.

8. Click Next. If necessary, make changes to the Task Mapping dialog box,

If you selected Merge the data into the active project in Step 6, you need to tell Project how to link the Access table with the project tasks. Highlight the field you will use to merge, typically the ID, and then click the Set Merge Key button.

9. Click Next. You see the final box of the Export Wizard, in which you can elect to save the map that you just defined.

If you selected the Resources and the Assignments check boxes as well as the Tasks check box in the Map Options dialog box, when you click Next, the Export Wizard displays additional boxes that are almost identical to the Task Mapping dialog box, but for each of these data types.

10. If you elect to save your map by clicking the Save Map button, Project displays the Save Map dialog box. Provide a name for the new map in the Map name text box.

Bringing Outlook task lists into Project

Perhaps you started a task list in Outlook's Task List, and now you realize that your list of tasks is really a project and you need the scheduling and cost features in Project. You don't need to start over—you can import the Outlook Task List into Project. Follow these steps to do so:

1. In Project, choose ToolsOImport Outlook Tasks. Project displays the Import Outlook Tasks dialog box, as shown in Figure 26-26.

2. Check the tasks that you want to import, and click OK. The tasks appear in Project. If the open project already contains tasks, the Outlook tasks are appended to the task list.

Bringing Word document information into Project

Unlike other types of files, you can't import Word files directly into Project. You can, however, include information in Word documents in a Project file by using one of the following techniques:

♦ You can paste information.

♦ You can link or embed information.

Note

^Note jlNote

I m p o rt 0 utlook Tasks _Q

Task Name

Notes

Duration

u

FnliW : I I-.L'.

=

HP Categnry : (none)

I ¡Taskl

ah

■ S Task 3

4h

u

.: Task 3

6h

u

■ 1 Task 4

3h

V

(

>

Figure 26-26: In this dialog box, mark the tasks that you want to import into Project.

Pasting information from Word into Project

When you use the paste method, you can paste the information either into a table view or into a note in Project. Pasting eliminates the extra step of retyping information.

If you paste text into blank rows, Project treats the information as new tasks or resources. If you paste information into fields that already contain information, Project replaces the information in those fields with the pasted information. However, you can't paste information into Project fields that contain calculated values, such as some of the fields in a cost table.

Note You can use the following technique to paste information from an Excel workbook into a Project table view, but first you must organize the information in your workbook to match the organization of a Project table. For example, suppose that you want to paste information into a resource sheet with the Entry table applied. Your workbook has 3 columns, but the resource sheet has 12 columns, and you want to paste the information into Columns 2, 5, and 8.

To paste this information, you need to create and apply a table in Project that displays only the fields that you intend to paste from your workbook. Make sure that the order and type of columns in the Project table match the order and type of information in the Excel table that you're pasting.

To paste information from Word into a Project table, follow these steps:

1. Open the Word document from which you want to copy information, and then copy the information to the Windows clipboard, as shown in Figure 26-27.

Outline.doc - Microsoft Word

File Edit

View Insert Format loots Table Window Help

- X

DcPQflïjijltâlS'ASî Eü, - l*y\m TOT Prandy NetCIeanup HJ IF iOO% -

i:

* Botf/text -

* ♦ ♦ + - 5tawn>Le™t - ='a ji] Updaté TOC □ B E?

^dk Normal

- Book Antigua - 12 - K I y S 'S 55 = I§ T î= E= î^ Fl - t A - _

Heading I

0 Ch ap ter 26 : Imp or tbxg cm d Exp or ting Pr^oject-

Inform a tion f

Heading 2

General-lmporting-and-Exporting-Concepts^

Heading 2

ExportinginformationU

Heading 3

«= Export in g-to-Offlce-fllesfl

Heading 3

= Export in g-Proj e ct-inform at ionto-a-graph ic-i mag efl

Heading 3

= Export ing-to- other-form at sfl

Heading 3

*» SavingProject-files-as-Webpages1[

Heading 2

G

lmporting-lnformation][

Heading 3

= Inserting-an other- project^

Heading 3

4 Importing Office- filesfl

Heading 3

= importing-other-filesfl

Heading 2

0

Troubleshooting U

Heading 3

= Project im ports-incorrect times-in'data'from-Micro soft-Ex ceil!

Heading 3

" Liniied-or-embedded-objects-dori't-importor-exportfl

Heading 3

=' The-export-flle-contains more-oMess-information-than-expectedfl

Heading 3

= Project'imports-invalid'information^)

Heading 3

= The-values of-imported-information-changeU

0

Heading 3

= The-im ported-project-ls-emptyf

= a H ! 1 i

U

m

Paqe 1

Sec 1 1Í1

Enqish (U.E CCi

Figure 26-27: A Word document that you can paste into a Project table.

Figure 26-27: A Word document that you can paste into a Project table.

2. Switch to Microsoft Project.

3. Switch to the view into which you want to paste the information. If necessary, choose ViewOMore Views. From the More Views dialog box that appears, select the view that you need and then click Apply.

4. Select the table into which you want to paste information by choosing ViewOTableOMore Tables. Choose the table that you want from the More Tables dialog box, and then click Apply.

5. (Optional) If the table that you select has columns that you don't need or is missing columns that you do need, you can add or hide columns. You can also add rows if necessary.

6. Click the first field in which you want information to appear after you paste.

7. Click the Paste button on the Standard toolbar. The information that is stored on the Windows clipboard appears in the Project table, as shown in Figure 26-28.

3 Microsoft Ptojerf . Projectl BBS

I*5: EMe Edit Mew Insert Format loo Is Eroject £o II olio rate Window Help rype a question fa-l»lp - S x

J -J A "Il I -A A y I. * -1 ^ > Tj^ ^ I * I NoSr'"11' ^IMlJji Detail Form

3 Microsoft Ptojerf . Projectl BBS

I*5: EMe Edit Mew Insert Format loo Is Eroject £o II olio rate Window Help rype a question fa-l»lp - S x

J -J A "Il I -A A y I. * -1 ^ > Tj^ ^ I * I NoSr'"11' ^IMlJji Detail Form

4

Show- I Arial

- 8 - B /

a ¡C] = SIillTaiki a a

Chapter 26: Importing and Exporting Project Information

o

M T W T F S

•Jtjn 29, tB S .Iii T W'T F IS

S : M T |W|T fjs

Jul 13/03

S |M jT |W|T |F |S

rTSifr

1

| Chapter 26: Importing and I

1 day?

2

General Importrig and Exp>

1 day?

«a

3

Exporting information

1 day?

S

4

Exporting to Office tiles

1 day?

s

5

Exporting Project intormotii

1 day?

S3

6

Exporting to other formats

1 day?

7

Saving Project files as V\tel

1 day?

¡3

8

Importing ^formation

1 day?

SI

9

Inserting another project

1 day?

S

10

Importing Office files

1 day?

S

1

11

Importing other fles

1 day?

a

12

Troubleshooting

1 day?

a

13

Projeci imports incorrect tir

1 day?

a

14

Unfced or embedded object

1 day?

Si

15

The export file contains mc

1 day?

HI

16

Projeci imports invaid infor

1 day?

s

17

The values of rrported iriifi

1 day?

13

The trported project is emi

1 day?

ai

19

Project displays imported ir

1 day?

H

20

Summary

1 day?

H

v

<

jJ JLU

►r

Ready

NUM

Figure 26-28: The information from the Word document appears in Project.

Figure 26-28: The information from the Word document appears in Project.

You can paste information from a Word document into a note in Project by using the same technique. Copy the information in Word to the Windows clipboard. Switch to Project, and double-click either the task or the resource to which you want to add a note. In the Task Information or Resource Information dialog box that appears, click the Notes tab. Then right-click the Notes area to display a shortcut menu, shown in Figure 26-29, and choose Paste. The information from Word appears in the Notes area.

Linking or embedding a Word document in Project

When you link or embed a Word document in Project, you actually insert the document as an object in your Project schedule, as follows:

♦ When you link a Word document to a Project file, the Project file reflects any changes that you make to the Word document.

♦ When you embed a Word document in a Project file, the Project file does not reflect subsequent updates to the Word document.

Project views objects that you insert as graphics. Therefore, you can link or embed a Word document as a graphic element in any graphics area of a Project file. A graphics area is any area in Project that can display picture information, including task, resource, or assignment notes; headers, footers, and legends in views; headers and footers in reports; the chart portion of the Gantt view; and the Objects box in a task or resource form.

Task Information _Q

General | Predecessors | Resources | Advanced Notes j Custom Fields

Name: I Exporting iniorrnatiori Duration: [ïd? W Estimated

jtl

'II

Ço i>y

Paste

Paste Special...

A

Font..

Object...

Bull Bis

Figure 26-29: You can use a shortcut menu to paste information from the Windows clipboard into a task or resource note.

To insert a Word document as a linked or embedded object, follow these steps:

1. Open a Microsoft Project file, and display the graphics area into which you want to insert a document.

2. Open the Insert Object dialog box, as shown in Figure 26-30.

Figure 26-30: Use the Insert Object dialog box to link a Word document to a Project file or embed a Word document in a Project file.

Tip To open the Insert Object dialog box in a task, resource, or assignment note, right-

click to display a shortcut menu and choose Object. To open the Insert Object dia-^ log box in the Gantt Chart view or Objects box, choose InsertoObject.

3. Select the Create from File option button.

4. Type in the path and filename of the document that you want to insert, or click Browse to locate and select the file.

5. Do one of the following:

• To link the object to the source document, select the Link check box.

• To embed the object, deselect the Link check box.

6. Click OK. Project displays a graphic image of your file, as shown in Figure 26-31.

You can use the handles around the image in the Task Information dialog box to move or resize the image.

Figure 26-31: A Word document inserted as a graphic image in a task note.

Tip By default. Project displays the contents of the file that you insert rather than an icon that represents the file. To display the object as an icon, select the Display As ' Icon check box in the Insert Object dialog box.

Tip You can delete the object by making sure that you see the handles that surround

% it and pressing Delete.

After you click OK, an icon appears in the Indicator column. When you slide the mouse over that icon, however, you don't see the contents of the note in the tip because it is a graphic image. Instead, you see a pair of single quotation marks, as shown in Figure 26-32.

Figure 26-32: When you link or embed an object in the note of a task or resource, you can't reveal the contents of the note by pointing at it with the mouse pointer.

Was this article helpful?

0 0
Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook


Post a comment