Exporting to Excel (Project 2002)

[MikeD]

Can anyone advise how to fix this please?

I'm using the Export Wizard with the Project Excel Template to save project
data to an Excel file.

In the resulting file, under the Assignment Table tab, is a column of Work.
The format is "80 hrs", "40 hrs" etc.

When I create a pivot table to sum this data by Resource it displays 0 in
every case. If I delete the " hrs" text from the entries in the Work column
then it's fine.

How can I get Project to export the Work data as numbers and not text so I
don't have to run a Find-and-Replace every time I do this?

Mike

 

[John]

Can anyone advise how to fix this please?

I'm using the Export Wizard with the Project Excel Template to save project
data to an Excel file.

In the resulting file, under the Assignment Table tab, is a column of Work.
The format is "80 hrs", "40 hrs" etc.

When I create a pivot table to sum this data by Resource it displays 0 in
every case. If I delete the " hrs" text from the entries in the Work column
then it's fine.

How can I get Project to export the Work data as numbers and not text so I
don't have to run a Find-and-Replace every time I do this?

Mike

Mike,
It isn't clear whether you are exporting the data directly to an Excel
Pivot Table or you are creating the pivot table after the export. With
an export map (to a pivot table or workbook), Project Work hours are
exported as text because the view display is being exported, not the
underlying Project database data. Text data in Excel cannot be
re-formatted using Format/cells.

Be advised that an export map only works with static Project data (i.e.
non-timescaled values). You might want to use the "analyze timescaled
data in Excel" utility/add-in found on the "analysis" toolbar. The
utility WILL export timescaled data (e.g. similar to one of the Usage
views) and it also exports the data directly from the underlying Project
database as a data type, (variant, if I recall correctly), that can be
converted to a number through Format/cells. The utility will not export
the static data (summed row values) but that can easily be accomplished
with a formula in Excel.

Of course another option is to write a custom VBA macro to export
whatever Project data you need and format it in any manner desired.

John
Project MVP

 

[MikeD]

Thanks for your very detailed reply.

It isn't clear whether you are exporting the data directly to an Excel
Pivot Table or you are creating the pivot table after the export.

I initially tried to export from Project directly to a Pivot Table, but I
couldn't get it to do what I wanted, which is to have Task Names as rows,
Resources as columns and summed Work hours in the body of the table. I don't
need any timescale values.

I'm currently exporting to a spreadsheet and importing the data into a pivot
table that I have created. The aim is to create a consistent way of
extracting this data that others can use to export from Project files to a
financial spreadsheet.

Of course another option is to write a custom VBA macro to export
whatever Project data you need and format it in any manner desired.

That presumes a level of programming competence that I don't possess (c:

We do have bright young things here who could do it for me though.

Thanks again,

Mike

 

[Haris Rashid]

hi Mike,

For the Number1 field associate the formula
Number1=CInt([Work])/60

As number1 will be a integer field it can be used in Excel for caluculation.

The division by 60 is in anticipation that the work is in hours and you may
change it depending how work is entered.

Kind regards,

 

[John]

Thanks for your very detailed reply.


I initially tried to export from Project directly to a Pivot Table, but I
couldn't get it to do what I wanted, which is to have Task Names as rows,
Resources as columns and summed Work hours in the body of the table. I don't
need any timescale values.

I'm currently exporting to a spreadsheet and importing the data into a pivot
table that I have created. The aim is to create a consistent way of
extracting this data that others can use to export from Project files to a
financial spreadsheet.


That presumes a level of programming competence that I don't possess (c:

We do have bright young things here who could do it for me though.

Thanks again,

Mike

Mike,
You're welcome and get those "bright young things" a workin'.

John
Project MVP