Multi years Enterprise Resource Cost/Rate setting

[Jason Leung]

Every year the service cost/rate to our customers will change and we have
been entering the new rates to the Enterprise Resource manually using the
cost rate table with corresponding effective dates. These Enterprise
Resources are categorized in around 30 rate groups so instead of updating
several thousand Enterprise Resources individually, is there any way to do
some form of batch update?
I could not find any database tables storing these new Enterprise Resource
rate. In addition, the Cost Rate table column in the Enterprise Resource
Global is not displaying the information unless I open it for each resource
individually.

 

[JackD]

I'm not sure about the database, but it is fairly easy to write some code to
do this.
The following adds a new payrate of $50/$75 (regular/overtime) starting Jan
1, 2006 to all resources in the project.

Sub setPayRates()
Dim r As Resource
Dim rs As Resources
Dim prs As PayRates

Set rs = ActiveProject.Resources
For Each r In rs
Set prs = r.CostRateTables("A").PayRates
prs.Add "1/1/2006", "$50/h", "$75/h", "$0"
Next r
End Sub

 

[Jason Leung]

JackD, thank you very much for your instructions. I think it is somewhat
close to what we need.
1. Can we do it for all the resources instead of qualifying it for only the
active projects. The reason is that we loaded the entire company resources as
Enterprise resources and only a small percentage have been assigned to active
projects at the beginning of the year. Doing it on active projects or even
including the completed projects will miss a lot of resources and when these
missed resources are assigned subsequently, they will still be having the old
rates.
2. We have approx. 30 rate groups and both the past year rates and the new
year rate are well defined. Perhaps we can do a batch update for all the
resources with last year rate say $50/hr to create a new entry having
effective date 2005/01/01 and rate $55/hr. In this case, we only need to run
the batch jobs 30 times which is much better than updating the individual
resource (thousands of times).

I'm not sure about the database, but it is fairly easy to write some code to
do this.
The following adds a new payrate of $50/$75 (regular/overtime) starting Jan
1, 2006 to all resources in the project.

Sub setPayRates()
Dim r As Resource
Dim rs As Resources
Dim prs As PayRates

Set rs = ActiveProject.Resources
For Each r In rs
Set prs = r.CostRateTables("A").PayRates
prs.Add "1/1/2006", "$50/h", "$75/h", "$0"
Next r
End Sub

 

[JackD]

"ActiveProject" in vba refers to the project which is currently open in the
project application. It does not matter if the project is being worked on or
not.

If your resources are in a resource pool, then open the resource pool and
run it there.

You can add more code to this so it determines which kind of resource rate
group the resource should get and then it updates the rate accordingly. You
would likely use a Select Case statement

Example

Sub setPayRates()
Dim r As Resource
Dim rs As Resources
Dim prs As PayRates
Set rs = ActiveProject.Resources
For Each r In rs
Set prs = r.CostRateTables("A").PayRates

Select Case r.resourcegroup
Case "Highly Paid People"
prs.Add "1/1/2006", "$500/h", "$750/h", "$0"
Case "Peons"
prs.Add "1/1/2006", "$5/h", "$7/h", "$0"
Case Else
prs.Add "1/1/2006", "$50/h", "$75/h", "$0"
End select

Next r
End Sub



--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

JackD, thank you very much for your instructions. I think it is somewhat
close to what we need.
1. Can we do it for all the resources instead of qualifying it for only the
active projects. The reason is that we loaded the entire company resources as
Enterprise resources and only a small percentage have been assigned to active
projects at the beginning of the year. Doing it on active projects or even
including the completed projects will miss a lot of resources and when these
missed resources are assigned subsequently, they will still be having the old
rates.
2. We have approx. 30 rate groups and both the past year rates and the new
year rate are well defined. Perhaps we can do a batch update for all the
resources with last year rate say $50/hr to create a new entry having
effective date 2005/01/01 and rate $55/hr. In this case, we only need to run
the batch jobs 30 times which is much better than updating the individual
resource (thousands of times).

 

[Jason Leung]

JackD, I think your second example is exactly what I need, so I set up my
Project Server 2003 test environment with only 2 Enterprise Resources, one
with $100/h and the other with 200/h to test out the VBA codes.
I opened the Enterprise Resource pool, selected both resources, and opened
the VBA Editor
I keyed in the following codes in the editor and ran Sub/UserForm (PF5) but
resulted Runtime Error "438", Object doesn't support this property or method.
Did I miss something?

Sub setPayRate()
Dim r As Resource
Dim rs As Resources
Dim prs As PayRates
Set rs = ActiveProject.Resources
For Each r In rs
Set prs = r.CostRateTabe("A").PayRates

Select Case r.reourcegroup
Case "Pay100"
prs.Add "1/1/2005", "$100/h", "$150/h", "$0"
Case Else
prs.Add "1/1/2005", "$200/h", "$250/h", "$0"
End Select
Next r
End Sub

 

[JackD]

Maybe the typos in your code?

Set prs = r.CostRateTabe("A").PayRates

Should be r.CostRateTable

Select Case r.reourcegroup

Should be r.ResourceGroup

 

[Jason Leung]

JackD, thank you very much for your prompt response. My typing skill is not
good so this time I cut and pasted your codes to the VBA Editor and just
modified the case to reflect the $100/h and $200/h rates I have on my
Enterprise Resources. I still encountered the same Run-Time error "438":
Object doesn't support the property or method. The codes are as follow.
Sub setPayRates()
Dim r As Resource
Dim rs As Resources
Dim prs As PayRates
Set rs = ActiveProject.Resources
For Each r In rs
Set prs = r.CostRateTables("A").PayRates

Select Case r.ResourceGroup
Case "Pay100"
prs.Add "1/1/2005", "$100/h", "$150/h", "$0"

Case Else
prs.Add "1/1/2005", "$200/h", "$250/h", "$0"
End Select

Next r
End Sub

 

[Jason Leung]

JackD, my VBA programmer was telling me I am missing the ResourceGroup
properties of the resource object. I am using Microsoft Project 11.0 Object
Library.
Regards

 

[JackD]

Now it is my turn for a mistake.

It should be r.group

Sorry.

 

[Jason Leung]

JackD, I fixed the r.group and this time it had no error but it did not add
the new set of rates to the resources. I also ran the first example you
provided where there is no case and is simplier but it also would not add the
new rates to the rsources neither. Regards

 

[Jason Leung]

JackD, I just got the problem resolved. Your codes including the cases are
working beautifully after I changed the rate arguments to numerics, e.g.
1/1/2006, 150, 200, 0.
Although the Resources table displays the rates in the format of $100.00/h,
they will not take anything other than numeric, i.e. they will not take the $
sign nor the /h.
I thank you very much for your help because your codes saved me tons of
work, not to mention the accuracy in updating several thousands of records
manually.
Best regards
Jason

 

[JackD]

Glad you worked it out. I tested mine in Project 2002 standard, so it is
interesting that it did not work in 2003 Pro. Thanks very much for posting
back with the resolution.

 

[Jason Leung]

JackD, for your information, I was using Project Pro 2003 to access Project
Server 2003. This is my development environment which I used for testing. Our
production environment is Project Pro 2002 and Project server 2002. Perhaps
if I tested your codes on our production environment, it would work the first
time. Thanks again.
Jason

 

[JackD]

Who knows. It may have to do with currency settings on the server? There are
a lot of things that I can't check without setting up a test environment.
Nothing I post here is guaranteed