Export to Excel

[TerryHolland]

I am new to MS Project. I have set up a project that has a number or
summary tasks each with a set of sub-tasks. Some of the sub-tasks also
have sub tasks.

What I want to be able to do is export tasks to Excel creating a sheet
that enables me to easily sum the number of 'work units' involved in
the project ie

Main 1 5 days
Sub 1 1 day
Sub 2 4 days
Sub 2 Sub 1 1 day
Sub 2 Sub 2 3 days

In Project it is obvious that the duration for Main 1 is the sum of the
times for Sub 1 & Sub 2 (which in turn is the sum of Sub 2 Sub 1 & Sub
2 Sub 2).

However, when this gets exported to Excel I get the following
WBS Name Duration
1 Main 1 5 days
1.1 Sub 1 1 day
1.2 Sub 2 4 days
1.2.1 Sub 2 Sub 1 1 day
1.2.2 Sub 2 Sub 2 3 days

This format makes it 'impossible' to derive the total amount of work
for project, Is there any way to isolate the figures that are actually
for work and not for summaries while still keeping the summaries in the
sheet for clarity?

 

[MSPLearner]

Not sure if I fully understand your question. Do you want the work (effort)
and duration of individual tasks that you then sum in EXCEL?

I use the Excel Pivot Table export function to dump pretty much any
information from MSP to Excel. Then use the power of EXCEL to do any
reporting.

MSPLearner

 

[Catfish Hunter]

If you have assigned resources to each task, go to View Resource Sheet. If
not there add the column Work (this is man-hours). This will show the total
hours for each resource and by resource. If you must export to Excel:

Analyze timephased data in Excel:

If you want to display timephased project information in a chart, you can
use the graphing features of Microsoft Excel by importing data directly from
Microsoft Project.
The Analyze Timescaled Data Wizard leads you through the process of
exporting and graphing timephased data from your project into Microsoft
Excel. The resulting Excel file shows information (such as work and cost)
about each task or resource on a per-day basis.
To run the Analyze Timescaled Data Wizard, click Analyze Timescaled Data in
Excel on the Analysis toolbar. To display the Analysis toolbar, point to
Toolbars on the View menu, and then click Analysis.

GOOD LUCK!!

 

[John]

I am new to MS Project. I have set up a project that has a number or
summary tasks each with a set of sub-tasks. Some of the sub-tasks also
have sub tasks.

What I want to be able to do is export tasks to Excel creating a sheet
that enables me to easily sum the number of 'work units' involved in
the project ie

Main 1 5 days
Sub 1 1 day
Sub 2 4 days
Sub 2 Sub 1 1 day
Sub 2 Sub 2 3 days

In Project it is obvious that the duration for Main 1 is the sum of the
times for Sub 1 & Sub 2 (which in turn is the sum of Sub 2 Sub 1 & Sub
2 Sub 2).

However, when this gets exported to Excel I get the following
WBS Name Duration
1 Main 1 5 days
1.1 Sub 1 1 day
1.2 Sub 2 4 days
1.2.1 Sub 2 Sub 1 1 day
1.2.2 Sub 2 Sub 2 3 days

This format makes it 'impossible' to derive the total amount of work
for project, Is there any way to isolate the figures that are actually
for work and not for summaries while still keeping the summaries in the
sheet for clarity?

TerryHolland,
Welcome to the "wonderful world" of Project......

First of all, don't confuse task duration with work. Duration is the
amount of working time, (as defined by the project calendar -
Project/Change Working Time), between the start of a task and the
finish. For summary lines, duration is NOT the sum of subtask durations,
unless all the subtasks just happen to be linked finish-to-start with no
lead or lag. For summary lines the duration is the working time from the
start of the first performance subtask to the finish of the last
performance subtask. "Performance" tasks are the real tasks wherein one
or more resources are assigned to complete it. Summary lines are NOT
performance tasks.

Work on the other hand is the effort required from one or more resources
to actually complete the task. If a single resource is assigned full
time to a given task, then the duration time and the work time will be
the same.

That's the short lesson, you might want to go to our MVP website at:
http://www.mvps.org/project/links.htm
and click the link to fellow MVP, Mike Glen's series on Project lessons
and techniques.

Given the above, there might not be a need for you to export to Excel at
all. What are you defining as "work units"? If it is the Work field,
Project already sums that up for you, and if it's not in the "cut" that
you want, it is likely the grouping feature of Project can be used to
provide the information. If you are really talking about duration, then
as stated above you do NOT want to simply sum things up.

Hope this helps.
John
Project MVP

 

[TerryHolland]

Thanks
I was unaware of the Work field and was entering into duration what
should have been work.

Another question about levelling.
I have a number of tasks that contain a number of subtasks and all
tasks are to be completed by a single person. Lets assume that none of
the tasks are Task Dependent ie no one task needs to be completed in
any particular order, but it makes sense to work on all of the
performance tasks within a summary task together before moving onto
another set of performance tasks. If I level, the way that tasks
become fragmented is makes the function unusable. Is there anyway that
I can set it so that all sub tasks of a summary task are kept together?

Does this make sense??

 

[John]

Thanks
I was unaware of the Work field and was entering into duration what
should have been work.

Another question about levelling.
I have a number of tasks that contain a number of subtasks and all
tasks are to be completed by a single person. Lets assume that none of
the tasks are Task Dependent ie no one task needs to be completed in
any particular order, but it makes sense to work on all of the
performance tasks within a summary task together before moving onto
another set of performance tasks. If I level, the way that tasks
become fragmented is makes the function unusable. Is there anyway that
I can set it so that all sub tasks of a summary task are kept together?

Does this make sense??

TerryHolland,
Well I'm glad we got the duration/work thing cleared up. Many new users
get those two mixed up.

Project's leveling algorithm uses multiple criteria to determine whether
a task is leveled. Many of these criteria are set in the Resource
Leveling user interface (Tools/Level Resources). In your case it may be
possible to set the date range such that only tasks under a given
summary line are leveled. If the time span for summary lines overlap,
then you could set the priority of all tasks to 1000 (i.e. do not level)
except those under the summary you do wish to level. Using this process
iteratively, you could level each summary group of tasks independently
but the end result may still have resource overallocation simply because
groups of tasks were independently leveled.

Hope this helps.
John
Project MVP

 

[Hung]

Hi

I'm interested in how to export data from MSP to Excel.
Can you show me how you can do it from Excel or MSP,
And whether that export can be "refreshed" the next time when I changed my
MSP?

I saw that Excel can read from Access database, but I did not see where it
can get from MSP

Thanks
Hung

 

[John]

Hi

I'm interested in how to export data from MSP to Excel.
Can you show me how you can do it from Excel or MSP,
And whether that export can be "refreshed" the next time when I changed my
MSP?

I saw that Excel can read from Access database, but I did not see where it
can get from MSP

Thanks
Hung

Hung,
There are various ways to exchange data between Project and Excel. It
depends on what type of data you want to export. If it is static data,
(i.e. non-timescaled), you can use an export/import map. If exporting
from Project go to File/Save As. In the lower part of the Save As window
you will see a "File of type" selection box. Select Microsoft Excel
Workbook or Pivot Table. When you hit Save, an export wizard will appear
and lead you through the steps. You can also import data from Excel
using a similar process except at the File menu select Open and set the
File of Type for an Excel Workbook.

If you need to export timescaled data (e.g. from one of the Usage
views), you can try using the built-in utility, "analyze timescaled data
in Excel" found on the Analysis toolbar. You will get different results
depending on whether the export is started from the Task or Resource
Usage views.

Another option similar to the above is to create your own custom VBA
macro to export/import the data. If you don't know VBA, we can help you
learn.

Yet another option is to query the Project database directly using SQL.

Hope this helps.
John
Project MVP

 

[John]

Thank you John very much for clarifying this.
Let me push this a little further to exactly what I'm doing now:
- I used to select a custom view in MSP with predefined filter to limit the
range I want to see in Gantt view
- Then I had to copy the tasks rows I selected and paste into Excel
- In Excel, a preformated file with all the formulas setup there already.
After the values I copied from MSP were pasted into a certain range, I got
all the analysis result ready.

I am wondering if I can eliminate the copy and paste action here by just a
click of a VBA macro or by refreshing data button to get it from some
queries.

Do you think that there is a quick way to do it?
TIA

Hung,
First of all, you're welcome.

"Quick way" is a relative term. If this is something you do on a regular
basis, then automating it with a custom macro is the way to go, in my
opinion. And yes, it can easily be set up to initiate the macro from a
toolbar button - that's how I set up my custom macros.

However, the custom macro approach is only good if you know how to write
VBA code, or if you know someone who can write it for you. If you would
like to learn Project VBA, fellow MVP, Rod Gill has recently written an
excellent book on that very subject and in it are some examples to do
what you want. For more information on how to get Rod's book, see:
http://www.projectvbabook.com

Hope this helps.
John
Project MVP