Export Project Data to Excel

[dknight555]

Hi

I posted this question a week ago but no one seems to have an answer -
I'll try once more just in case someone has an idea. Else I will
approach Microsoft and then post their response.

I am trying to export data from Project 2003 to Excel but using the
analyser option it fails to export all the required data as does
saving the file as a workbook.

In Resource Usage I can see all the resources in my master project
with all the linked projects. I can see the assignments days (planned
and actual) by week per project. However I cannot export the data
into Excel.

What I want to see in Excel is:
week dd/mm/yy dd/mm/yy dd/mm/yy
John Smith proj A 2 3 4
proj B 2 1 0

The idea is to add other rows in Excel with availability and totals so
that under/over usage can be highlighted.

In addition I would like to add the assignees team, say in Group, and
copy this field at the same time as the rest of the export. I could
then sort on teams (Groups) to get overall team assignments/
availability. When you have a department of almost 100 staff anything
that shows overall assignment/availability is helpful. Unfortunately
my company does not want to purchase a resource management tool so I
stuck with the limitations of Project.

Any help gratefully received as I do not want a heavy sell from
Microsoft to purchase more of their products.

Thanks

David

 

[Jim Aksel]

I recall your question. Since the Analyze Timephased Data in Excel does not
work for you (and I agree it will not show you that view), you will need to
do it using Visual Basic for Applications (VBA). You can write a routine
that will wake up Excel and populate it as you please.

Not to worry, Microsoft is not going to try to sell you anything... it is
already installed on your machine. Open MS Project and then ALT+F11 or
Tools/Macros/Visual Basic Editor.
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[John]

Hi

I posted this question a week ago but no one seems to have an answer -
I'll try once more just in case someone has an idea. Else I will
approach Microsoft and then post their response.

I am trying to export data from Project 2003 to Excel but using the
analyser option it fails to export all the required data as does
saving the file as a workbook.

In Resource Usage I can see all the resources in my master project
with all the linked projects. I can see the assignments days (planned
and actual) by week per project. However I cannot export the data
into Excel.

What I want to see in Excel is:
week dd/mm/yy dd/mm/yy dd/mm/yy
John Smith proj A 2 3 4
proj B 2 1 0

The idea is to add other rows in Excel with availability and totals so
that under/over usage can be highlighted.

In addition I would like to add the assignees team, say in Group, and
copy this field at the same time as the rest of the export. I could
then sort on teams (Groups) to get overall team assignments/
availability. When you have a department of almost 100 staff anything
that shows overall assignment/availability is helpful. Unfortunately
my company does not want to purchase a resource management tool so I
stuck with the limitations of Project.

Any help gratefully received as I do not want a heavy sell from
Microsoft to purchase more of their products.

Thanks

David

David,
One additional comment to Jim's reply. Visual Basic for Applications is
indeed a built-in part of Project, as it is with virtually all Microsoft
applications, but unless you know how to write VBA macros, it is going
to take some time learning VBA in order to get what you want. If you
want to learn how to write VBA code for Project, fellow MVP, Rod Gill
has an excellent book out on that very subject. For more information, go
to, http://www.projectvbabook.com.

If you do not have any VBA experience and don't have time to learn, you
could post to our sister newsgroup at,
microsoft.public.project.developer and see if anyone is interested in
writing the code for you.

John
Project MVP

 

[noelaprospero]

Maybe you have already tried it: just copy columns, both from table (left
pane) and from timephased right pane, to Excel.
I can give you hope that with Project 2007 things are much more easier.
Meanwhile I understand your frustration with Excel analyzer in 2003.
I can tell you that after trying every option both in analyzer, and in save
as, and in export, the best option was to copy and it is really simple; and,
may I add, it is a truly "WYSIWYG" tool. You select columns, you copy , you
paste in Excel, and everything gets there exactly as you need it.
Good luck