Export Resource Data to Excel

[davegb]

I am trying to recreate a report I saw in Excel, imported from
Project. It displayed a page for each resource in the resource pool.
In the upper left cell was the resource's name, then the column
headers. Below that was an "overallocated hours" row, filterable with
Autofilter. Below that were remaining availble hours, and total work
hours. below that was the list of tasks the resource is assigned to.
Across the top are Work, Actual Work, Start, Finish, the a column for
each week over a specified time period. The work on each task for the
week was displayed in each column.
Does anyone know how to create this type of report? Any help would be
greatly appreciated.

Dave Bellamy

 

[John]

Dave,
It sounds like someone either did a lot of copy and pasting or wrote a
custom VBA macro to format the Project resource data exactly as they
wanted.

The custom macro approach is the most convenient once the code is
written but If you do not have any experience with VBA you can get most
of the report data by using a combination of an export map
(non-timephased project data) and the "analyze timescaled data in Excel"
add-in (for timesphased data). Once the data is in Excel, it can be
manipulated into whatever format is desired.

Hope this helps.
John

 

[John Sitka]

Dave, how did you get on with this? I need similar.

Thanks

 

[davegb]

John,
Any ideas on how to export all of the resource usage tables for a
groups of resources in the resource pool? Each resource on a separate
tab in the spreadsheet?
Dave

 

[John]

Dave,
Yes, there are probably several methods. The most obvious (although not
the simplest or easiest in the end) is a cut and paste from Project to
Excel.

A second method is to use the "analyze timescale data in Excel" add-in
found on the "Analysis" toolbar. This is a big step up from cut and
paste but will still require manual re-organizing of data in Excel

For a third possible method, see my response to Dawn Crosier (dated
6/22/04) in the microsoft.public.project.vba newsgroup.

The fourth method (my choice) is to write a VBA macro to perform the
export and format the Excel workbook all in one fell swoop.

Hope this helps.
John