Forum Discussion
Issues Trying To Export Resource Usage Info From Project To Excel
I am trying to export the resource usage info for my project to MS Excel so people who don't have Project can view this information. The left pane which displays the Resource Name, and assigned tasks below it as well as additional fields I've added export fine to excel, however, when I click in the top left corner of Resource Usage Details pane, copy and paste that info into excel, the row headers such as Work and Overalloc. as well as column headers that show the dates don't transfer over to excel. Maybe I'll recommend Microsoft Fix this. Here's an example of what I end up getting when I copy the info into Excel:
Is there another way that I can get resource usage data from project into another format like excel, so that resources know how many hours they are projected to spend on a given task during a certain period?
4 Replies
Is there another way? Yes. Are you up for some VBA? I've done this many times over the years for many users so it's likely I already have a macro that can give what you want.
Would you be willing to send your file to me along with the details on exactly what you want to see? I will ask some questions.
John
Really? VBA would be required to get extract the Resource Usage View info to Excel? I guess if you want to give it a try. Here is the link to the MS Project File. What I'm looking for is to be able to export the information displayed in both panes of the resource usage view, including headers to Excel. Here is the link to the MS Project File.
https://drive.google.com/file/d/1fW4Y-BdlY4xGIm7iRP8pWQcxKM8rWM-o/view?usp=sharing
Is VBA required to extract Project data to Excel? No, but unless you are really good using Pivot tables and are willing to make some compromises, VBA gives the user much more functionality and freedom than an export using Project's Visual Reports.
I took a quick look at the file and right off the bat I noticed a couple of disturbing things. Foremost are the links on summary lines. That is bad practice and is going to cause you a ton of headaches. You should read this Wiki article:
https://social.technet.microsoft.com/wiki/contents/articles/29569.ms-project-linking-andor-assigning-resources-to-summary-tasks.aspxThe second thing I noticed is there are several resources that are overallocated and I don't remember from your previous posts if that is an issue you are trying to resolve. At least one resource (VEC developer) can be resolved by changing the leveling options from "day by day" to "month by month" but that may not be appropriate for your needs.
One other item I notice is that every task shows the Standard calendar as the Task Calendar. This is unnecessary since by default a "none" in the Task Calendar field means the task follows the Project calendar, which in your case is a modified Standard. And instead of directly editing the Standard calendar, best practice is to create a custom calendar, perhaps based on the Standard, when adding company/national holidays.
Okay, now that it may seem like I "crashed your party", what exactly would you like to see in Excel?
For reference, the screen shot below is the output (Excel) of your file using one of my existing macros. If you want to pursue this, it will be easier for us to work directly via e-mail. My contact info is below. Again, I will ask some questions.
John
jmacprojataticlouddotdotcom
(remove obvious redundancies)
