×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Import Resource information into Effective Date and Standard Rate ??

thread913-1597472

Import Resource information into Effective Date and Standard Rate ??

Import Resource information into Effective Date and Standard Rate ??

(OP)
Hello,

I am new to MS Project (2007) and learning what I can out of text books and on the internet.  I have been asked, among other things, to create a resource sheet in project that includes employee information for my company.  I used the import wizard to import pertinent data from excel to project.  

The problem that I am having is this.  My company is contracted to do multi-year jobs by our customers.  Over the course of these jobs, our employees (resources) receive raises.  Short of manual entry, which would be extremely laborious for a company this size, is there any way for me to change the Effective Date, Standard Rate and Overtime Rate cells accessed by Resource Sheet > Resource Information > Costs.  

Thanks in advance,

Scott

RE: Import Resource information into Effective Date and Standard Rate ??

There is no built-in automatic way to do this.  You'll have to write some VBA.

RE: Import Resource information into Effective Date and Standard Rate ??

(OP)
I tried to play around with the macro creater inside of project to no avail.  The cells where you input effective date, standard rate and overtime rate are not copy/pastable.  I am not a programmer so I am unsure of how it may be possible to create a vba macro for this task.  Do you have any recommendations on where I could seek help in writing something?

Thanks

RE: Import Resource information into Effective Date and Standard Rate ??

This will get you started

CODE

Sub PDQBach()
Dim res As Resource

Dim iCostRateTables As Integer
Dim iEntries As Integer

For Each res In ActiveProject.Resources
    Debug.Print res.Name; " "
    For iCostRateTables = 1 To 5  'Rate A (default); B; C; D; E
        For iEntries = 1 To res.CostRateTables(iCostRateTables).PayRates.Count
            Debug.Print "Rate "; iCostRateTables; " row "; iEntries; " Eff Date:"; _
                Format(res.CostRateTables(iCostRateTables).PayRates(iEntries).EffectiveDate, "YYYY MMM dd"); " ";
            Debug.Print " Std Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).StandardRate; " ";
            Debug.Print " OVT Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).OvertimeRate
        Next
        Debug.Print
    Next
    Debug.Print: Debug.Print
Next
End Sub
 

RE: Import Resource information into Effective Date and Standard Rate ??



Hi,

I know next to nothing about MS Project.  However, I am quite conversant with VBA, and may be able to shed some light on some general things.

In the VB editor there is an Object Browser.  You'll find some help there.  Here's what I found...

There is a Resources Collection containing Resource objects.

For any Resource object there is a StandardRate property and an OvertimeRate property.

As far as dates, I found Date1 thru Date10, Start1 thru Start10 & Finish1 thru Finish10.

I suppose that you could do something like this...

CODE

Dim RES as Resource, sRES as string
For i = 1 to ListLimit
'pseudo code here, that you figure out
  sRES = GetResourceNameFromMyList(i)

  for each RES in ActiveProject.Resources
    if sRES = RES.Name then
       RES.StandardRate = GetResource_STD_RATE_FromMyList(i)
       RES.OvertimeRate = GetResource_OT_RATE_FromMyList(i)
       Exit For
    end if
  Next
next
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Import Resource information into Effective Date and Standard Rate ??

(OP)
PDQBach,

I pasted your code into a macro file and ran it but I have a feeling that the field names on my resource sheet did not quite match up to the names in the code.  I went ahead and changed the column headers to Std Rate and OVT Rate but still didn't get an additional row in Resource Cost with the updated information like I was hoping.  Please forgive my lack of knowledge in programming language.  Any additional pointers would be most welcome.  And again, thank you for your time.

 

RE: Import Resource information into Effective Date and Standard Rate ??

Show me your code, please

RE: Import Resource information into Effective Date and Standard Rate ??

(OP)
This is the code I ran.  Verbatim what you posted.  If there was something else I was supposed to do please let me know.

Sub PDQBach()
Dim res As Resource

Dim iCostRateTables As Integer
Dim iEntries As Integer

For Each res In ActiveProject.Resources
    Debug.Print res.Name; " "
    For iCostRateTables = 1 To 5  'Rate A (default); B; C; D; E
        For iEntries = 1 To res.CostRateTables(iCostRateTables).PayRates.Count
            Debug.Print "Rate "; iCostRateTables; " row "; iEntries; " Eff Date:"; _
                Format(res.CostRateTables(iCostRateTables).PayRates(iEntries).EffectiveDate, "YYYY MMM dd"); " ";
            Debug.Print " Std Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).StandardRate; " ";
            Debug.Print " OVT Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).OvertimeRate
        Next
        Debug.Print
    Next
    Debug.Print: Debug.Print
Next
End Sub

 

RE: Import Resource information into Effective Date and Standard Rate ??

I don't understand what problem you're experiencing.  You wrote "I pasted your code into a macro file and ran it but I have a feeling that the field names on my resource sheet did not quite match up to the names in the code."

I have no idea what you're feeling but the code I gave you works for P2007 (and P2003 and, quite likely, P2002, P2000, and P98).

Which fields are you not seeing that you expected to see?  What values are you not seeing that you expected to see?

 

RE: Import Resource information into Effective Date and Standard Rate ??

(OP)
Running the code didn't produce values for effective date, standard rate or overtime rate in the Resource Cost tab of Resources which is what I was hoping to achieve originally.  I changed my field headers in Resource View to mirror the names I saw in your code hoping that would do the trick.  If I was a more sophisticated programmer I would give you a better description, then again, I probably wouldn't be looking for help winky smile  Thanks again for the help though.

RE: Import Resource information into Effective Date and Standard Rate ??

When you wrote "Short of manual entry, which would be extremely laborious for a company this size, is there any way for me to change the Effective Date, Standard Rate and Overtime Rate cells accessed by Resource Sheet > Resource Information > Costs." it seemed to me that you were trying to import the dates-and-rate-changes for the future -- and that will be an arduous manual task -- so I gave you, as a starting point, some code that will let you access the fields (their names and access path are not intuitively obvious).  And, since the code only contained debug.print statements it should have been obvious that nothing would be updated elsewhere in the Project file.

Now I see that what you really have intended is to have code move already capture dates-and-rate-changes information and use it to update a couple of cost fields (Standard Rate, Overtime Rate) on View > Resource Sheet.

Why?

Project will automatically update the two Rate fields on the Effective date.  

You say it will be extremely laborious ... what's laborious waiting for time to pass?

RE: Import Resource information into Effective Date and Standard Rate ??

(OP)
We are on the same page now.  

In order to get project to update a resource's standard and overtime rates on a future effective date, you have to double click on a resource, go to the cost tab and manually enter those three fields.  I would like to bypass this step and use a macro to automatically pull the data and fill in these fields for me to avoid that portion of the manually entry.  I realize that on the effective date project will automatically use the correct values.

Am I missing something simple here?  

Thanks  

RE: Import Resource information into Effective Date and Standard Rate ??

Try this (watch out for linewraps):

CODE

Option Explicit

Sub PDQBach2()
Dim oRes As Resource
Dim oCRT As CostRateTable
Dim msXLapp As Object
Dim iRow As Integer
Dim varOpenDataFile As Variant

Dim dteTemp As Date
Dim strTempStd As String, strTempOVT As String, strTempUse As String


'Load Excel
On Error Resume Next
Set msXLapp = GetObject(, "Excel.Application")
If Err.Number = 0 Then
    msXLapp.Application.Quit
    Set msXLapp = Nothing
    Set msXLapp = CreateObject("Excel.Application")
Else
    Set msXLapp = CreateObject("Excel.Application")
End If
msXLapp.Visible = True
On Error GoTo 0

'Open spreadsheet with data
    'ColA = resource name
    'ColB = a valid date field
    'ColC = "$", amount, "/", "h" - e.g. $500/h
    '                          h = hour
    '                          d = day
    '                          m = month
    'ColD = same as format as colC
    'ColE = "$", amount - e.g. $0
    
varOpenDataFile = msXLapp.GetOpenFilename("Excel workbooks (*.xls), *.xls", , "Open (DataFile)")
If varOpenDataFile = False Then
    End
End If
msXLapp.Workbooks.Open FileName:=varOpenDataFile
msXLapp.Sheets(1).Activate

'Loop through each MS Project Resource
For Each oRes In ActiveProject.Resources
    'Brute force lookup loop to find matching resource name in Excel spreadsheet
    For iRow = 1 To msXLapp.ActiveSheet.cells.SpecialCells(xlCellTypeLastCell).Row
        If msXLapp.cells(iRow, 1) = oRes.Name Then
            'Choose "A" (Default) rate table
            Set oCRT = oRes.CostRateTables("A")
            dteTemp = msXLapp.cells(iRow, 2)
            strTempStd = msXLapp.cells(iRow, 3)
            strTempOVT = msXLapp.cells(iRow, 4)
            strTempUse = msXLapp.cells(iRow, 5)
            'Params are: effective date (mm/dd/yyyy), StdRate (d=day; h=hour; m=month), OVTRate, CostPerUseRate
            'For example:
            'oCRT.PayRates.Add "07/01/2012", "$500/h", "$600/h", "$0"
            oCRT.PayRates.Add CStr(dteTemp), strTempStd, strTempOVT, strTempUse
        End If
    Next
Next
MsgBox "Done"
End Sub

RE: Import Resource information into Effective Date and Standard Rate ??




FYI

Although your Excel row number in your Excel Sheet used range may not exceed 32767, if it ever does, your iRow variable, declared as INTEGER, will overflow.

Best to declare all variables used for row numbers as LONG.

CODE

Dim lRow As Long
BTW, I am learning a lot from the code you have posted, PDQBach.
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Import Resource information into Effective Date and Standard Rate ??

Good point, Skip.  Thanks.

(In my defence here, it's somewhat unlikely that he will have more than 32,000 resources ... but in the general, yup, working with rows really should use a Long.)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close