Monthly Hours per Resource

[Lee]

Hi All,

I'm trying to create a VBA Macro to export the hours per month per resource
to a CSV file. The "Resource Usage (Work)" report is almost exactly what I
want to produce but has two custom fields missing that I want in my exported
data.

Can anyone tell me how this report would be created in VBA? If so, I could
work the rest out for myself...

Thanks in advance for any help

Regards

Lee

 

[Jan De Messemaeker]

Hi Lee,

Look up the help for the Timescaledata method.
HTH

 

[Lee]

Thanks Jan.

Can you tell me what is wrong with this code: -

For r = 1 To ActiveProject.Resources.Count
MsgBox
ActiveProject.Resources(r).TimeScaleData(ActiveProject.ProjectStart,
ActiveProject.ProjectFinish, TimeScaleUnit:=pjTimescaleMonths)
Next r

I want to output the hours assigned per resource on a monthly basis.

Thanks!

Lee

 

[Jan De Messemaeker]

Hi,

Your timescaledata method returns a timescalevalues object which does not
show readily
I don't have the help at hand biut shouldn't you also show the type of data
(pjtimescaledwork, or...)
I don't know but before I ever dared use this method I did a thourough
reading of the help.
You might be on your way fro 10 trial and error go's

Meanwhile to illustrate the Timescalevalues thing

Set TSV=
ActiveProject.Resources(r).TimeScaleData(ActiveProject.ProjectStart,
ActiveProject.ProjectFinish, TimeScaleUnit:=pjTimescaleMonths)
for ctr=1 to tsv.count
Dataforthismonth=tsv(ctr).value
next ctr

HTH

 

[Lee]

Jan,

Thanks for that, I have almost exactly what I want. The only thing I am
struggling with is my column heading for the months of the project.

How do I get the values Jan, Feb, Mar, etc from these TimeScaledValues in
this format?

Thanks for all your help so far!

Lee

 

[Jan De Messemaeker]

Hi Lee,

Quoting from memory I use the Month function and the DateAdd Function (both
from the VBA library)
In the loop add (ctr-1) months to Start and take the Month of that date.

OBTW, something that is not weel known.
ProjectStart is what is set in Project Information, it is NOT NECESSARILY
the start of the project.
That one is ProjectSummaryTask.start

HTH