adding tasks, resources and assignments to a project using VBA

[Mark VII]

Greetings --

I'm working up a program to take a Excel list of tasks and resources and
bring it into Project. (We have a standard estimating Excel template that is
currently entered into Project manually, and am trying to automate this.) Am
having trouble assigning resources to tasks. Here's a high level of how the
code looks.

dim tsk as Task
dim rst as Resource
dim asn as Assignment
dim proj as Project

set proj as Application.Projects.Add
For each row on the input spreadsheet
set tsk = proj.tasks.add
tsk.Name = < task name from spreadsheet > '* this goes OK

proj.Resources.Add (< resource name from spreadsheet > ) '* this goes OK
lngResourceId = RetreiveResouceId(name) '* function to find resource
and get its id

'* trouble starts here
Set asn = tsk.Assignments.Add(tsk.ID, lngResourceId, <units from
spreadsheet>)
asn.Work = < hours from spreadsheet >

next input row

Creating the task records goes OK. So does adding the resource and
retrieving the resulting ID. Where it gets messy is creating the resource
assignments.

The "Set asn = tsk.Assingment...." gives 1101, The Argument Value is not
Valid if I retrieve the units value from the spreadsheet (even if it's 1).
However, the statement works OK if I hard code the units value to 1.

If I run with units hard coded to 1, and reach the "asn.Work = < value from
spreadsheet >" line, I get the 1101 error again.

Have searched high and low for an example of the correct way to specify
these values and come up dry. Does anyone have any suggestions?

Thanks a million.

Mark

 

[John]

Greetings --

I'm working up a program to take a Excel list of tasks and resources and
bring it into Project. (We have a standard estimating Excel template that is
currently entered into Project manually, and am trying to automate this.) Am
having trouble assigning resources to tasks. Here's a high level of how the
code looks.

dim tsk as Task
dim rst as Resource
dim asn as Assignment
dim proj as Project

set proj as Application.Projects.Add
For each row on the input spreadsheet
set tsk = proj.tasks.add
tsk.Name = < task name from spreadsheet > '* this goes OK

proj.Resources.Add (< resource name from spreadsheet > ) '* this goes OK
lngResourceId = RetreiveResouceId(name) '* function to find resource
and get its id

'* trouble starts here
Set asn = tsk.Assignments.Add(tsk.ID, lngResourceId, <units from
spreadsheet>)
asn.Work = < hours from spreadsheet >

next input row

Creating the task records goes OK. So does adding the resource and
retrieving the resulting ID. Where it gets messy is creating the resource
assignments.

The "Set asn = tsk.Assingment...." gives 1101, The Argument Value is not
Valid if I retrieve the units value from the spreadsheet (even if it's 1).
However, the statement works OK if I hard code the units value to 1.

If I run with units hard coded to 1, and reach the "asn.Work = < value from
spreadsheet >" line, I get the 1101 error again.

Have searched high and low for an example of the correct way to specify
these values and come up dry. Does anyone have any suggestions?

Thanks a million.

Mark

Mark,
My first guess is that the units value in Excel is of the wrong data
type, like maybe a text value. A similar thing may hold for the work
value in Excel. Is it in hours (i.e. "10h")? Also note that the basic
work unit in Project is minutes, so you will need to convert the hours
in Excel to minutes for Project. Otherwise the work values will be a
whole lot smaller than you expect (i.e. 10 minutes instead of 10h).

Hope this helps.
John
Project MVP

 

[Mark VII]

My first guess is that the units value in Excel is of the wrong data
type, like maybe a text value. <

Hi John -- Good point. If I assign the contents of the relevant spreadsheet
cell to a variable, then assign the variable to the Work property, it works.
Go figure.

Have run into a new problem, though. As I loop through the resources and
populate their work values, numbers start changing strangely.

For example, I assign resource1 to the task and let Units default to 100%.
Then, I set his Work property to specify his hours. So far so good. (At
this point, the task Work = resource1's work.) Then, I assign resource2 to
the task, let his units default to 100%. Still OK. When I set resource2's
work hours, I would expect project to set resource2's hours to the specified
value, leave resource1's hours alone, and set the task work hours to the sum
of resource1 and resource2's hours.

Instead, project reduces resource1's work hours. Also, the task total work
hours is a value lower than expected. Any thoughts here?

Thanks,
Mark

 

[John]

Hi John -- Good point. If I assign the contents of the relevant spreadsheet
cell to a variable, then assign the variable to the Work property, it works.
Go figure.

Have run into a new problem, though. As I loop through the resources and
populate their work values, numbers start changing strangely.

For example, I assign resource1 to the task and let Units default to 100%.
Then, I set his Work property to specify his hours. So far so good. (At
this point, the task Work = resource1's work.) Then, I assign resource2 to
the task, let his units default to 100%. Still OK. When I set resource2's
work hours, I would expect project to set resource2's hours to the specified
value, leave resource1's hours alone, and set the task work hours to the sum
of resource1 and resource2's hours.

Instead, project reduces resource1's work hours. Also, the task total work
hours is a value lower than expected. Any thoughts here?

Thanks,
Mark

Mark,
The "numbers" are probably changing because of the default task type. My
guess, by the scenario you describe, is that the task type is set for
fixed work. With a fixed work task, adding a second resource to a task
that already has one resource will cause the first resource's hours to
be adjusted. That is because the hours of the first resource sets the
fixed work value for that task and adding a second resource causes
Project's work equation to recalculate the original resource's work to
maintain the original work value for the task.

Hope this helps.
John
Project MVP

 

[Mark VII]

The "numbers" are probably changing because of the default task type. <

I think that was it. I changed my code, and though not exactly as expected,
the hours allocations for multiple resources per task are now much closer.

Thanks a million for helping me get this program on line. It's going to
save us a TON of time.

Mark

 

[John]

I think that was it. I changed my code, and though not exactly as expected,
the hours allocations for multiple resources per task are now much closer.

Thanks a million for helping me get this program on line. It's going to
save us a TON of time.

Mark

Mark,
You're welcome and thanks for the feedback. We generally try to save a
poster at least 2 tons of time but I guess a ton will be OK this
time....

John
Project MVP