rate changes on effecitve date - print out

P

Peggy Cornett

toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
Does any have VBA code to get a list that includes all enterprise resources,
their resource code, and their hourly rate in one file from the enterprise
resource pool? I am changing rates on an effective date and I need three
columns to show hourly
rates, one for each of the next three years. This information is in the cost
table for each resource. We want to do some comparisons.
Thanks,
~Peg Cornett
 
R

Rod Gill

Hi Peggy,

The following runs on the Enterprise Resource Pool (must be open and active
in Project Pro)

Sub ResourceDetails()
Dim Res As Resource
Dim Pay As PayRate
Dim str As String
For Each Res In ActiveProject.Resources
If Not Res Is Nothing Then
str = Res.Name & ", " & Res.Code
For Each Pay In Res.PayRates
str = str & ", " & Format(Pay.EffectiveDate, "Short Date") &
"-" & Pay.StandardRate
Next Pay
Debug.Print str
End If
Next Res
End Sub


Produces the following in the Immediate Window:
Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
1/01/2009-$121.00/h
Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
1/01/2009-$121.00/h
Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
1/01/2009-$121.00/h


--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx
 
P

Peggy Cornett

I opened the Enterprise Resource Pool, selected all resources read only to
view. I then copied the code below into VB Editor, pressed F8 to step through
the code and got a compile error on str = str & ", " &
Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
~Peg
 
R

Rod Gill

Hi Peg,

That code worked fine in Project 2003 Prof SP2. Try just using:

str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate

If this does not work, try deleting more and more of the line until it does
work! In addition under Tools, References, are there any missing references?
--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx
 
P

Peggy Cornett

My stars! With red face I confess, I was stepping through by pressing F8 and
totally drew a blank that that was the reason it was stopping! I realized
this before I even tried it again. I pressed run and of course it works just
like you said. Hopefully this confession will help others try new things and
take baby steps with VBA as I am beginning to do.

Now, I want to get this info into Excel rather than just the immediate
window. I can do that but probably not as efficient as you would suggest.
Is this where I would create a .txt file to hold the data?

Thanks for your patience and I hope you and others are chuckling with me.
Peggy
 
P

Peggy Cornett

I did remove ""-" &" from before Pay.StandardRate.
Thanks for you help. This is great!
Peg
 
P

Peggy Cornett

In my enterprise resource pool I have 804 active resources. When I run the
macro the return in the immediate window begins with resource line 606 and
ends with 808. Why aren't the first 605 being returned?
Thanks!
Peggy
 
P

Peggy Cornett

Yes, all 804 resources are open. Yes, they have a pay rate. I ran the code
multiple times and get the same results. When I include the inactive
resources there are 1008 total. The results from the code still returns only
partial but is consistant if I re-run it. Note, regardless of what resources
I run it against I only get 199 resources in the result and they are the last
199 listed rather than the first.
Thanks for your help.
Peggy
 
P

Peggy Cornett

How do I code this to print out the results to an excel sheet or text file?
I can only get 199 records to print to the immediate window. I can get those
into excel but how can I get all records and put them directly into a file
rather than the immediate window?
Thank you for your help.
Peggy Cornett

Sub ResourceDetails()
Dim Res As Resource
Dim Pay As PayRate
Dim str As String
txt.FilePath = "C:\test resource effective date.txt"
For Each Res In ActiveProject.Resources
If Not Res Is Nothing Then
str = Res.Name & ", " & Res.Code
For Each Pay In Res.PayRates
str = str & ", " & Format(Pay.EffectiveDate, "Short Date")
& " - " & Pay.StandardRate
Next Pay
End If
Next Res
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List of resources 7
Maintenance of Cost Rate Tables in MS Project 2010 0
Cost rate tables help? 2
Project Server 2007 Multiple resource rate 3
Resource Rate for Resource Pool 5
Time Phased Actual Work updates for Project Resources? 2
Add custom cost rate to rate tables 2
Import rates from excel via macro 6

Top