MS Project export to Excel

[mz11v2]

Hi,

Basically, I'm trying to export the data in have in a particular MS Project View into an Excel worksheet. Details are as follows:-


In Project I have the "Task Usage" view setup with the timescale (along the top) at a granularity of "weeks".
Down the side I have the names of Rolled-up tasks subdivided into individual tasks, which are in turn subdivided into names of people who will work against the task. In the adjoining column I have defined and hand-populated a custom field called "booking code". The "body" of the table shows how many hours the person (or task) is planned to consume in the appropriate week.

I want to get this whole view into Excel pretty much "as-is". I've tried 3 approaches :-


EXPORT
The Project "SaveAs" option seems to offer some promise but none of the standard "Export Mappings" offered appears to preserve the "weekly" breakdown of the data; they all do an unwanted roll-up of one type or another. Also, I cannot see how to set up a custom Export Mapping to get exactly what I want.

REPORT GENERATION
I've also played with the Report generation capability within Project. I have experimented with custom report formats of type "crosstab". I can get a report that looks almost as I require except for two problems :-
1) I cannot get BOTH the task name and booking code columns on the "side headings" of the report
2) The Reports are directed to a printer...how do I intercept and get into Excel instead?


COPY/PASTE
Simply copy/pasting cells isn't particularly satisfactory - it doesn't copy the whole "view". The best I have been able to achieve is to copy/paste it in two chunks; the first to copy/paste the task names and booking codes columns, and the second to copy/paste the weekly breakdowns. Even this leaves out the date headings at the top...so I have to add them into Excel afterwards by hand....So, although this method "works" its extremely clunky and is rather too error prone for my requirements


Any ideas/advice gratefully received.


Many Thanks
Rob

 

[tgrantsr2001]

Did you try Edit...copy picture? You won't have workable data but you will have your end results.

 

[mz11v2]

Hi,

Thanks for the suggestion...but I need the data to be workable once it's in Excel.
Cheers
Rob

 

[jancebk]

Rob:

I think you want to export your work in an Excel Pivot Table format, which summarizes data in crosstab calculations. When you save it, it will let you choose to export tasks, resource and/or assignment info.

Can you get access to the Que book "Special Edition Using MS Project 2000" by Tim Pyron? (Big book - over 1200 pages, I highly recommend it for any serious MS Project user, worth the $40.00 price many times over (IMHO)) It details step by step how to do the type of exporting I think you want to do.

Hope this helps!
Janice

 

[scottborth]

get the plug-in "Analyze Timescaled Data in Excel". although i'm not sure about it exporting the custom column that you set up.

or you can cut/paste-link (text) the table at the left into Excel and have it auto-update upon opening of the MS Excel spreadsheet. when doing this, be sure to create a copy of the view (Task Usage) for specifically this purpose as i've had links broken via hiding and inserting columns.