Forum Discussion
Exporting resources to excel to create histogram
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 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
Chris_Mercer,
Wow, three duplicate postings, and three separate replies. My colleagues suggest a "simple" copy and paste of the Project Resource Usage data into an Excel Workbook and that will work fine, it just takes a few steps. I'm more about automation, particularly if you need to do the export on a regular basis.
You say "Budget Work Units" but I don't think that is really what you mean. The Budget Work field is only applicable to the Project Summary Task and the file you posted to OneDrive has no Budget Work.
As I mentioned, I have a few macros that export Project timescaled data to Excel. One in particular is very close to what you need. As currently configured, it produces three Work fields for each resource, but that is easy to reconfigure. Here is sample outut of the macro run on your file. If you are interested in an automated approach, let me know.
John
5 Replies
Chris_Mercer,
Wow, three duplicate postings, and three separate replies. My colleagues suggest a "simple" copy and paste of the Project Resource Usage data into an Excel Workbook and that will work fine, it just takes a few steps. I'm more about automation, particularly if you need to do the export on a regular basis.
You say "Budget Work Units" but I don't think that is really what you mean. The Budget Work field is only applicable to the Project Summary Task and the file you posted to OneDrive has no Budget Work.
As I mentioned, I have a few macros that export Project timescaled data to Excel. One in particular is very close to what you need. As currently configured, it produces three Work fields for each resource, but that is easy to reconfigure. Here is sample outut of the macro run on your file. If you are interested in an automated approach, let me know.
John
Hi John, thanks for the reply
Not sure what is going on with all the posts! everything seems to go through for moderation and then sends an invalid error, so inevitably I try again and at some point it then posts everything!!
I managed to copy and paste the Resource Usage data into excel as you suggest above, just need to clean it up on Monday morning & graph it now.
I'm definitely interested in the macros - anything to make it easier in the future!
Cheers
Chris_Mercer,
This forum has some quirks. The owners of the forum went through some "tweaks" some months back and in my view (as per usual), it wasn't broken but they fixed it anyway. For example, a posting or reply does show the date but it no longer shows the time and threads aren't displayed in chronological order, so it's harder to follow.
Anyway, if you are interested in a VBA solution I'd like to work with you one-on-one via email. You can contact me at the address below. I will ask some question.
John
jmacprojataticlouddotdotcom
(remove obvious redundanices)
Chris_Mercer,
To answer your basic question, "is it possible?" Yes, the best way I have found to get data from Project to Excel is via VBA. Sometimes the methods you tried will work and I laud you for giving them a go.
I have a few different macros that export Project resource data to Excel. Specifically which fields would you like to export? And, are you looking for data on a daily, weekly, or monthly basis?
Would you be willing to send me your Project file? I find it easier to help someone when I have the actual file.
John
Hi John - Thanks for replying
VBA's are a bit beyond me at the moment (I need to learn but am struggling to find the time!)
I'm just looking to export Task Name/Duration/Start/Finish/Resource Name/Budget Work Units (with the work units data phased over time) so I can build a resource histogram in Excel showing the numbers of various site resources per week.
The .mpp file is here -https://1drv.ms/u/c/5b76311aac56837f/Eavsh25a-iVJpbFeszHsANMBgMHcWMXd9KN-VEbUrFUdKA?e=aPPUVu
Any issues, let me know
Thanks in advance!
