Forum Discussion
JoeCuomo
Jan 23, 2024Copper Contributor
macro to export resource usage information
Hi!
I am new to MS Project and would like to write a VBA macro for Project to write an excel of peak units for each resource by month for a given date range. The goal is to use this date in a PowerBI report to display department utilization over the next year. Any help would really be appreciated.
thank you!
- John-projectSilver ContributorJoeCuomo,
Yeah, I have a macro that exports monthly resource usage info to Excel. It would take a little tweaking to provide the exact format you want but that's pretty straightforward. If the manual process proposed by Ignacio doesn't meet your needs, let me know.
John- vivekkumar10Copper Contributor
Hi John,
I'm using Project 2016 Standard, and have Excel 2013, I need to create a report for management based on MS Project data real time (on save MSP), highlighting critical tasks, issues & delays from baseline. could you please help, as I feel I'm stuck with the inbuilt export & it does not give me hierarchy of tasks as each column, rather task name is the only column export using wizard.
I came across your previous answers and found them really useful, but those threads were locked (long ago), could you please help solve my issue.
Many thanks,
VK
- John-projectSilver Contributor
This really should be a new post instead of tagging unto an existing post. I suggest you post a new question with a little more detail on exactly what you are trying to do (a screen shot mock up of the report you want would be very helpful). Then me or one of my colleagues will be in a better position to offer help.
John
- Ignacio_MartinIron Contributor
Hello Joe.
Perhaps before writing a macro, you could test whether the information displayed in the Resource Usage view is useful for what you are looking for.
You can try the following:1. From the Resource Usage View, select: Peak Units in the Menu Resource Usage Format > Details > Add Details > Usage Details > Peak Units (Avaliable fields) > Show >> OK and deselect Work. Although the Work field may also be useful to you.
2. Choose the time scale for the resource unit values you want to display in: Menu View > Zoom > Months, for example.
3. If you want to hide the assignments and only see the Resources: Select all the resources by clicking in the upper left corner of the table (above 1), and then Menu > View > Data > Outline > Hide Subtasks.
4. Copy and paste the table on the left into Excel, selecting in the upper left corner (above 1) and Ctr+C to copy and paste in Excel.
5. Repeat the Copy and Paste by selecting the Time Phase on the right, clicking in the corner of the phase (Details) and Ctr+C to copy and paste in Excel, below, in row 2.Some sample images are attached
Notes:
- The numbers of units shown are the maximums of the chosen periods of the time scale.- The date header of the time scale is not copied to the clipboard, so you must generate it in Excel.
If this procedure does not work for you, then you can make a macro that requires some experience, especially for this case.
I hope I've helped.
Ignacio