Exporting Resource Usage to Excel

[Daviv]

I want to export the resource usage to a excel worksheet. I want to include
the resource, date, hrs for the duration of the project in the following
format:

8/1 8/2 8/3
Painter 8 8 4
Plasterer 8 8 8

 

[John]

I want to export the resource usage to a excel worksheet. I want to include
the resource, date, hrs for the duration of the project in the following
format:

8/1 8/2 8/3
Painter 8 8 4
Plasterer 8 8 8

Daviv,
For versions of Project prior to Project 2007 you can use the "Analyze
timescale data in Excel" utility found on the Analysis toolbar. Project
2007 added a little more flexibility by introducing Visual Reports found
under the Report menu.

However, for any version of Project, VBA can be used to export and
format any Project data.

John
Project MVP

 

[Daviv]

I am using project 2003. If anyone can help with a vba, it will be greatly
appreciated.

 

[Mike Glen]

Hi Daviv,

For vba, try posting on the microsoft.public.project.developer newsgroup.
Please see FAQ Item: 24. Project Newsgroups. FAQs, companion products and
other useful Project information can be seen at this web address:
http://project.mvps.org/faqs.htm

Mike Glen
Project MVP

 

[John]

I am using project 2003. If anyone can help with a vba, it will be greatly
appreciated.

Daviv,
Yes we can help with VBA but did you try the "Analyze timescale data in
Excel" utility I mentioned? Your report format is pretty simple and the
utility will provide that data. A VBA macro is only required if you need
something more complex.

John
Project MVP

 

[Daviv]

I tried "Analyze timescale date in Excel". But when I tried to "export
data", I get an error message "Make sure Excel 5.0a later is installed". I
have excel 2003 installed?

 

[John]

I tried "Analyze timescale date in Excel". But when I tried to "export
data", I get an error message "Make sure Excel 5.0a later is installed". I
have excel 2003 installed?

Daviv,
Wow, that's weird. I've never seen that error message pop up. Obviously
the code behind the utility is unable to start Excel. Try these things
and see if any of them help.
1. First, do you have the latest update, (SP3), for Project 2003
installed? It shouldn't matter, but who knows.
2. Try opening Excel first and then open Project and run the "Analyze
timescale data in Excel". By the way to get the output you want, you
need to run the utility from the Resource Usage view in Project.
3. This also shouldn't matter but try it anyway. Close all instances of
Excel. Then open your Project file. Go to Tools/Macro/Visual Basic
Editor. When the editor window appears (it should be blank with only the
VB toolbar showing). Go to Tools/References. Find Microsoft Excel 11.0
Object Library in the list of available references and check the box
next to it. Hit "OK". Return to Project and try the utility.

John
Project MVP

 

[DJ]

Hi all,

This is exactly what I'm looking for - I'm trying to build an automated
process (VB) that exports resource data in the format mentioned here. Ideally
I'd like a macro in excel that takes this information from the open project
so that the end result is

PLANNED RESOURCE ALLOCATION:

W1 W2 W3 W4
PERSON 1 5 5 5 5
PERSON 2 5 5 5 5

PERSON 3 5 5 5 5
PERSON 4 etc 5 5 5 5

I would really really really appreciate some help in building this. I
imagine it shouldnt be too difficult because the data relativley easy and
this information is already available via analyse function in MS project. Im
running MS project standard 2003 and excel 2003.

DJ

 

[JulieS]

Hi DJ,

Have you tried the Analyze Timescaled Data in Excel option? Show
the Resource Usage view, and on the Analysis toolbar, there is a
button which will launch a wizard allowing you to export timescaled
resource information.

I hope this helps. Let us know how you get along.

Julie
Project MVP

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

 

[John]

Hi all,

This is exactly what I'm looking for - I'm trying to build an automated
process (VB) that exports resource data in the format mentioned here. Ideally
I'd like a macro in excel that takes this information from the open project
so that the end result is

PLANNED RESOURCE ALLOCATION:

W1 W2 W3 W4
PERSON 1 5 5 5 5
PERSON 2 5 5 5 5

PERSON 3 5 5 5 5
PERSON 4 etc 5 5 5 5

I would really really really appreciate some help in building this. I
imagine it shouldnt be too difficult because the data relativley easy and
this information is already available via analyse function in MS project. Im
running MS project standard 2003 and excel 2003.

DJ

Daviv,
I see this is a continuation of a previous post to which I responded.
What happened when you tried the three steps I suggested in my last
response? Do you still get the error message about Excel 5.0a when
trying to run the "Analyze Timescaled Data in Excel" utility?

John
Project MVP

 

[Harley Quinn]

I'm looking for something similar, but I need the task breakdown as well, and
when I try the "Analyze Timescaled data in Excel" it doesn't break it down to
task level (like you'd see if you hit the + on the resource name in Project).
Any way to include that in the export?

 

[JulieS]

Hi Harley,

The default "Analyze" wizard does not pull out the name of the
Assignment, nor the detail of the work per assignment. There is no
default way of showing both the timescaled (day by day or week by
week) data. You can carefully copy and paste from both sides of the
Resource Usage view into excel or you can create some VBA code to
export and format. If you are interested in the code and have some
skills with VBA, I suggest posting to the Developer newsgroup.

I hope this helps. Let us know how you get along.

Julie
Project MVP

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

 

[MarkC]

This is exactly what I am looking for. I will go to Developer side.
Cheers,
Mark

 

[JulieS]

Good luck Mark.

This is exactly what I am looking for. I will go to Developer
side.
Cheers,
Mark