How can I mass-enter revised standard rates for resources in Proje

[Roger G]

I have a number of MS Project plans with a large number of resources in them
(100+). I wish to apply a revised standard cost rate for groups (grades) of
resource from a specific date. It looks like the only way to do this is to
laboriously enter each resource record and amend the cost rate table. Is
there any way that I can mass enter the revised rates (e.g. through the
resource sheet view) in the same way that I was able to make the original
entry of standard rate?

 

[Roger G]

Roger,
Yes there is a much easier way. Off hand I can't think of a way to do it
without using VBA but with a simple macro the task becomes very quick
and accurate (compared to the potential for human error using manual
entry).

If you haven't dabbled in VBA we can help you, just let us know. Are you
trying to change ALL the resource rates as of a given date or just some
of the resources?

Hope this helps.
John
Project MVP

John,
Thanks for your reply. I would like to apply revised rates to every
resource with effect from a specific date (not the same rate for the every
resource). I've dabbled in macros and VBA but I'm far from being an expert.

Thanks very much,
Roger

 

[John]

I have a number of MS Project plans with a large number of resources in them
(100+). I wish to apply a revised standard cost rate for groups (grades) of
resource from a specific date. It looks like the only way to do this is to
laboriously enter each resource record and amend the cost rate table. Is
there any way that I can mass enter the revised rates (e.g. through the
resource sheet view) in the same way that I was able to make the original
entry of standard rate?

Roger,
Yes there is a much easier way. Off hand I can't think of a way to do it
without using VBA but with a simple macro the task becomes very quick
and accurate (compared to the potential for human error using manual
entry).

If you haven't dabbled in VBA we can help you, just let us know. Are you
trying to change ALL the resource rates as of a given date or just some
of the resources?

Hope this helps.
John
Project MVP

 

[John]

John,
Thanks for your reply. I would like to apply revised rates to every
resource with effect from a specific date (not the same rate for the every
resource). I've dabbled in macros and VBA but I'm far from being an expert.

Thanks very much,
Roger

Roger,
In pseudo-code I would probably do something like the following
(assuming the resources are identified by groups):
1. Pop a user query asking for the date for the new rate to take effect
2. Set the view to "Resource Sheet"
3. Set up an outer loop to repeatedly apply the resource group filter.
One method to indicate when you are done (i.e. all groups have been
updated) is to enter no group name.
4. Set an "area" for the filtered resources. Note: if the area is null,
you're done. Detect that and exit.
5. For each resource in the area set the new pay rate using the "add"
method on the PayRate object of the resource CostRateTable.

The code could be further automated by using a pre-coded look up table
matching resource groups to pay rates. Then you wouldn't need to
manually apply the group filter and enter group names.

At least this is one approach.

John

 

[Roger G]

John,
I've recorded a macro where I selected a resource from the resource
sheet view, then clicked on the cost rate table, entered an effective date
and a new standard rate for that effective date, and then saved the record. I
looked at the VBA code for the macro and all it does is select the resource
record. When I run the macro that's all it does too, so it looks like the
resource cost rate table is immune from macros. I have MS Project 2000 as
that is the current corporate standard. I think I understand your coding
suggestions, but if I can't enter or update the cost rate table I don't see
how they will work.
Roger G

Roger G

 

[Steve House [MVP]]

Your note says you'd like to apply a new rate for resources on a certain
effective date but it won't be the same rate for all resources. How would
an automated process such as a macro know what rate to apply to what
resource? Is there some common attribute of every resource that can be used
to detect that Joe's new rate should be $15 per hour but Bob's will be $20??
IF not, a macro solution is barking up the wrong tree and you're going to
have to do it by hand.

 

[Roger G]

Steve,
There is a resource attribute that I can refer to to determine
which resource should receive a particular rate. I could set up the macro to
update a defined set of resources at each pass if only I could find out how
to gain access to the second row of the rate table by macro.

regards,
Roger G

 

[Steve House [MVP]]

Not a VBA guru here so I don't know for sure. Try posting in the Project
Developer's group, someone there may have some ideas. I'm a bit skeptical
whether it's possible, but I might be wrong.
--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs