Printing Resource Usage view data to Excel

[Lucas Soler]

I'm looking for the VBA code that can send the Left-Hand-Side of the Resource
Usage view to Excel (I have the VBA code to do this from the Gantt Chart view
only). I'd like my report to look like the following:

TASK NAME REMAINING WORK
Task A 100
Resource 1 90
Resource 2 10
Task B 10
Resource 1 10

Can anyone provide the VBA code? Extremely appreciated.
Ciao.

 

[John]

I'm looking for the VBA code that can send the Left-Hand-Side of the Resource
Usage view to Excel (I have the VBA code to do this from the Gantt Chart view
only). I'd like my report to look like the following:

TASK NAME REMAINING WORK
Task A 100
Resource 1 90
Resource 2 10
Task B 10
Resource 1 10

Can anyone provide the VBA code? Extremely appreciated.
Ciao.

Lucas,
If you already have code for the Gantt Chart view, why can't you simply
modify that? Nonetheless, here is some pseudo code that should get you
started:

1. Open Excel and pre-format a Worksheet with the headers
2. Use this loop structure to read the Project data

For Each t in ActiveProject.Tasks
[write t.Name and t.RemainingWork to Worksheet cells]
For each a in t.Assignments
[write a.ResourceName and a.RemainingWork to Worksheet cells]
Next a
Next t

(Be aware that the value for RemainingWork will come across in minutes
so the value must be divided by 60 to get hours).

Hope this helps.
John
Project MVP

 

[Lucas Soler]

Extremely helpful - it worked. Thanks.

I'm looking for the VBA code that can send the Left-Hand-Side of the Resource
Usage view to Excel (I have the VBA code to do this from the Gantt Chart view
only). I'd like my report to look like the following:

TASK NAME REMAINING WORK
Task A 100
Resource 1 90
Resource 2 10
Task B 10
Resource 1 10

Can anyone provide the VBA code? Extremely appreciated.
Ciao.

Lucas,
If you already have code for the Gantt Chart view, why can't you simply
modify that? Nonetheless, here is some pseudo code that should get you
started:

1. Open Excel and pre-format a Worksheet with the headers
2. Use this loop structure to read the Project data

For Each t in ActiveProject.Tasks
[write t.Name and t.RemainingWork to Worksheet cells]
For each a in t.Assignments
[write a.ResourceName and a.RemainingWork to Worksheet cells]
Next a
Next t

(Be aware that the value for RemainingWork will come across in minutes
so the value must be divided by 60 to get hours).

Hope this helps.
John
Project MVP

 

[Lucas Soler]

How about for something like the following:

RESOURCE/TASK ACTUAL WORK
Pepe LePue
Task a 10
Task f 10

Any ideas?

Extremely helpful - it worked. Thanks.

I'm looking for the VBA code that can send the Left-Hand-Side of the Resource
Usage view to Excel (I have the VBA code to do this from the Gantt Chart view
only). I'd like my report to look like the following:

TASK NAME REMAINING WORK
Task A 100
Resource 1 90
Resource 2 10
Task B 10
Resource 1 10

Can anyone provide the VBA code? Extremely appreciated.
Ciao.

Lucas,
If you already have code for the Gantt Chart view, why can't you simply
modify that? Nonetheless, here is some pseudo code that should get you
started:

1. Open Excel and pre-format a Worksheet with the headers
2. Use this loop structure to read the Project data

For Each t in ActiveProject.Tasks
[write t.Name and t.RemainingWork to Worksheet cells]
For each a in t.Assignments
[write a.ResourceName and a.RemainingWork to Worksheet cells]
Next a
Next t

(Be aware that the value for RemainingWork will come across in minutes
so the value must be divided by 60 to get hours).

Hope this helps.
John
Project MVP

 

[John]

How about for something like the following:

RESOURCE/TASK ACTUAL WORK
Pepe LePue
Task a 10
Task f 10

Any ideas?

Lucas,
Sure. You basically interchange the loops (and of course pull Actual
Work). Something like this:

For Each r in ActiveProject.Resources
[write stuff to Worksheet]
For Each a in r.Assignments
[write stuff to Worksheet]
Next a
Next r

Hope this helps.
John
Project MVP