Import Resource information into Effective Date and Standard Rate ??

[mercerbear]

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

 

[PDQBach]

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

 

[mercerbear]

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

 

[PDQBach]

This will get you started

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

 

[SkipVought]

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...
[tt]
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.
[/tt]
I suppose that you could do something like this...

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

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mercerbear]

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.

 

[PDQBach]

Show me your code, please

 

[mercerbear]

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

 

[PDQBach]

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?

 

[mercerbear]

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 ;-) Thanks again for the help though.

 

[PDQBach]

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?

 

[mercerbear]

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

 

[PDQBach]

Try this (watch out for linewraps):

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

 

[SkipVought]

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.

Dim lRow As Long

BTW, I am learning a lot from the code you have posted, PDQBach.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PDQBach]

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.)