Export of Resource Usage with VBA

[RK]

Hi,

I just started programming in VBA with MS Project 2003.

I want to create an Excel export by using the object model of MS Project.

The export works fine, but how can I get the number of days a team member is
working on a task considering a a period (e.g. 1 month) and all the splits
and parallel tasks.

I tried the Splitpart object, that shows only the start and finish but not
the work :-(.

Example:

A task starts on Nov. 26th with 10d work. The task is splitted into three
splits: 11/26-12/02 (5d), 12/15-12/17 (3d), 12/31-01/03 (2d).

Expected result of the calculation: 6d for this task.

I know that I can export the Resource Usage View but this is not the way I
want so solve this, because it depends on the view settings.

Thanks,

Reiner Kuehn

 

[John]

Hi,

I just started programming in VBA with MS Project 2003.

I want to create an Excel export by using the object model of MS Project.

The export works fine, but how can I get the number of days a team member is
working on a task considering a a period (e.g. 1 month) and all the splits
and parallel tasks.

I tried the Splitpart object, that shows only the start and finish but not
the work :-(.

Example:

A task starts on Nov. 26th with 10d work. The task is splitted into three
splits: 11/26-12/02 (5d), 12/15-12/17 (3d), 12/31-01/03 (2d).

Expected result of the calculation: 6d for this task.

I know that I can export the Resource Usage View but this is not the way I
want so solve this, because it depends on the view settings.

Thanks,

Reiner Kuehn

Reiner,
First lets separate out duration from work. Your post says you want to
get the number of days a resource is working on a task during a period.
However that is not necessarily the same as the number of hours that
resource is working on the task. The SplitPart collection will give the
duration portions and you could probably use that information coupled
with the resource's Work content in the task to get the work in each
split but why not use a simpler method.

If the VBA code references Project objects directly (background
processing) rather than through object selection (foreground processing)
the view settings are irrelevant. That is because direct object
referencing pulls data directly from the Project database instead of the
data as it is presented in a particular view. Your best bet for getting
task, resource or assignment information on a timescaled basis is to use
the TimescaleData Method. More information about this method can be
found in the Project object browser and help file.

The example provided is unclear as to exactly what you want. I can only
guess that the "expected result" is the number of work hours expressed
as days for a resource that worked on the task either part time or in
conjunction with other resources. Nonetheless, take a look at the
TimescaleData Method. With it, you can effectively get any kind of cut
at the data you need although it may take some additional data
manipulation in the code if what you need is not available directly as a
built-in Project field.

Hope this helps.
John
Project MVP