Forum Discussion
Exporting resources to Excel
Hi I'm a scheduler who normally uses P6 (although I have used MS Project a little in the past!)
I'm working on a tender schedule which has a stipulation to be presented in MS Project... all good so far, schedule built looks good, hits the key dates etc and now I've loaded it out with construction resources (masons/roofers/electricians etc), one of the tender deliverables is to present a resource histogram (showing numbers of operatives/day) of the project resource.
I'm looking to export the project resources to excel to produce the histogram and this is where the issue is! The options as I see it are -
- Export a visual report - I exported a time-phase resource usage report but then hit a brick wall when I tried to manipulate the pivot table data to add a calculated field to convert the hours into a resource headcount as project export to Excel using OLAP cube data which isn't modifiable!
- Resource Usage View copy & paste - next I tried to straight up copy & paste the raw data from the Resource Usage view into excel to create my own pivot table but unfortunately left clicking to highlight all the date just didn't seem to work - I could highlight and copy the left hand table but the time phase data on the right hand side seems un-copyable, if I highlight it I don't get any copy option on the left click!
- Export to Excel - next I tried to export the raw data in xlsx format via File/Export/Save Project as File but then am unsure of which fields to set up when mapping to get the resources time phased, I just seem to be able to export the tabular info -
Lastly I created a custom report using the in-built graphing tool in Project to create the histogram but this gives a couple of issues - the resource stats on the y-axis are in hours (obviously) with no obvious way of converting to days and more importantly it is in Project and not in Excel as stipulated in the tender requirements!
Am I missing something fundamental with this/is it possible?
Any help would be grateful - thanks
Hi Chris
It takes two steps to copy the Resource Usage view:
First, select the entire table by clicking in the top left corner of the table and Copy or Ctrl+C to paste into Excel.
Second, select the time phase on the right by clicking in the top left corner of the phase (title Details), and Ctrl+C to copy and paste into Excel.
Date titles in the timescale are not copied, and you'll have to generate them in Excel, although it's quick and easy (two consecutive dates and drag...).
Also, you can choose to display the assigned resource units in the time phase in: Resource Usage Format > Add Details > Select and Show Peak Units in Available fields of the left list
... or even deselect Work field to simplify copying to Excel.If you want to hide tasks and only show the totals for each resource: select the entire table and View > Data > Outline > Hide Subtasks.
I hope this helps.
Ignacio
6 Replies
Hi Chris
It takes two steps to copy the Resource Usage view:
First, select the entire table by clicking in the top left corner of the table and Copy or Ctrl+C to paste into Excel.
Second, select the time phase on the right by clicking in the top left corner of the phase (title Details), and Ctrl+C to copy and paste into Excel.
Date titles in the timescale are not copied, and you'll have to generate them in Excel, although it's quick and easy (two consecutive dates and drag...).
Also, you can choose to display the assigned resource units in the time phase in: Resource Usage Format > Add Details > Select and Show Peak Units in Available fields of the left list
... or even deselect Work field to simplify copying to Excel.If you want to hide tasks and only show the totals for each resource: select the entire table and View > Data > Outline > Hide Subtasks.
I hope this helps.
Ignacio
Great - Thanks for replying... yes that works finally managed to copy the LH side time phased data!
Since this is a duplicate of your ater post, you might consider deleting this one as I responded to the later one.
John
Hi yes - I'm not sure what has gone on with the posts... I kept getting an error when I was waiting for moderation and then somehow posted it multiple times!
Hi - yes, I'm not sure what has gone on with this... I kept get an invalid error message when I tried to post and then somehow has posted multiple times!
Chris_Mercer,
Again, I see your current response is duplicated. Unfortunately I'm not a moderator on this forum so I can't combine or delete your duplicate posts. Maybe one of my MVP colleagues will clean things up a bit.
