none
Mass update of cost rate tables and effective dates? RRS feed

  • Question

  • We are starting to forecast finances for schedules into 2013 and beyond in MSPS 2010.  We need to put in the 2013 rates with effective dates of 1/1/13 so the new rates are applied on about 900 resources.  I've seen the thread here which asked about the exact same thing a couple of years ago but it wasn't much help.

    Was wondering if anyone knew of any 3rd party tools or VBA that we could use to help with this process?

    Thanks!

    Monday, October 29, 2012 9:09 PM

Answers

  • For the life of me I can't find the link to the page where I was finally able to modify the code to a point that worked for what I needed so if this colde looks familiar to anyone, please let me know so I can provide the credit.  Rene Alvarez's code above is close, but I made some further modifications.

    Here is the code I ended up with which allows me to bulk delete all pay rates except for the current year, and then apply new rates for 1/1/13 and on:

    -------------------------------------------------------
    Update rate table "A" with future year rates
    -------------------------------------------------------
    Sub UpdateRates()
    Dim Res As Resource
        For Each Res In ActiveProject.Resources
        If Res.Type = pjResourceTypeWork Then
            Res.CostRateTables("A").PayRates.Add "01/01/2013", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2014", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2015", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2016", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2017", "3.0%", "", ""
        End If
        Next Res
    End Sub
    -------------------------------------------------------
    Delete everything but current rates
    -------------------------------------------------------
    Sub ClearPayRates()
    Dim Res As Resource
    Dim pr As PayRates
        For Each r In ActiveProject.Resources
        If Not r Is Nothing And r.Type = pjResourceTypeWork Then
            For i = 1 To 5
                Set pr = r.CostRateTables(i).PayRates
                pr(1).OvertimeRate = 0
                pr(1).CostPerUse = 0
                If pr.Count > 1 Then
                    For j = pr.Count To 2 Step -1
                        pr(j).Delete
                    Next j
                End If
            Next i
        End If
    Next r
    End Sub
    -------------------------------------------------------
    Delete ALL rates on all rate tabs
    -------------------------------------------------------
    Sub ClearPayRates()
    Dim Res As Resource
    Dim pr As PayRates
        For Each r In ActiveProject.Resources
        If Not r Is Nothing And r.Type = pjResourceTypeWork Then
            For i = 1 To 5
                Set pr = r.CostRateTables(i).PayRates
                pr(1).StandardRate = 0
                pr(1).OvertimeRate = 0
                pr(1).CostPerUse = 0
                If pr.Count > 1 Then
                    For j = pr.Count To 2 Step -1
                        pr(j).Delete
                    Next j
                End If
            Next i
        End If
    Next r
    End Sub


    DJ Johnson

    Monday, December 3, 2012 5:08 PM

All replies

  • DarenJ --
     
    I am not aware of any 3rd party tools, but you could certainly use VBA for a mass update of cost rates for lots of resources.  To pursue this solution, I would recommend you repost your question in the Project Customization and Programming user forum at:
     
     
    Doing so will allow our VBA experts to analyze your problem and suggest some VBA code.  Hope this helps.
     

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Tuesday, October 30, 2012 1:53 AM
    Moderator
  • Hello DarenJ.

    I believe the best option is to do it with VBA. Maybe this code can help you. It is a piece of code for something else but I think it can point you to the right direction.

    Private Sub cmdAsignar_Click()
        Dim res As Resource
        Dim x As Integer
        With ActiveProject.Application.ActiveSelection
            For x = 1 To .Resources.Count
                Set res = .Resources(x)
                res.CostRateTables(1).PayRates(1).StandardRate = txtTablaA.Value
                res.CostRateTables(2).PayRates(1).StandardRate = txtTablaB.Value
                res.CostRateTables(3).PayRates(1).StandardRate = txtTablaC.Value
                res.CostRateTables(4).PayRates(1).StandardRate = txtTablaD.Value
                res.CostRateTables(5).PayRates(1).StandardRate = txtTablaE.Value
            Next
        End With
    End Sub
    Rene Alvarez
    Tuesday, October 30, 2012 1:58 AM
  • Hi,

    Below site discusses about "Update Resource Cost rate Using PSI"

    http://epmxperts.wordpress.com/2011/01/30/update-resource-cost-rate-using-psi/

    Tuesday, October 30, 2012 2:55 AM
  • Thank you for the suggesitons.  I will look into these oprtions further.

    DJ Johnson

    Tuesday, October 30, 2012 1:14 PM
  • For the life of me I can't find the link to the page where I was finally able to modify the code to a point that worked for what I needed so if this colde looks familiar to anyone, please let me know so I can provide the credit.  Rene Alvarez's code above is close, but I made some further modifications.

    Here is the code I ended up with which allows me to bulk delete all pay rates except for the current year, and then apply new rates for 1/1/13 and on:

    -------------------------------------------------------
    Update rate table "A" with future year rates
    -------------------------------------------------------
    Sub UpdateRates()
    Dim Res As Resource
        For Each Res In ActiveProject.Resources
        If Res.Type = pjResourceTypeWork Then
            Res.CostRateTables("A").PayRates.Add "01/01/2013", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2014", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2015", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2016", "3.0%", "", ""
            Res.CostRateTables("A").PayRates.Add "01/01/2017", "3.0%", "", ""
        End If
        Next Res
    End Sub
    -------------------------------------------------------
    Delete everything but current rates
    -------------------------------------------------------
    Sub ClearPayRates()
    Dim Res As Resource
    Dim pr As PayRates
        For Each r In ActiveProject.Resources
        If Not r Is Nothing And r.Type = pjResourceTypeWork Then
            For i = 1 To 5
                Set pr = r.CostRateTables(i).PayRates
                pr(1).OvertimeRate = 0
                pr(1).CostPerUse = 0
                If pr.Count > 1 Then
                    For j = pr.Count To 2 Step -1
                        pr(j).Delete
                    Next j
                End If
            Next i
        End If
    Next r
    End Sub
    -------------------------------------------------------
    Delete ALL rates on all rate tabs
    -------------------------------------------------------
    Sub ClearPayRates()
    Dim Res As Resource
    Dim pr As PayRates
        For Each r In ActiveProject.Resources
        If Not r Is Nothing And r.Type = pjResourceTypeWork Then
            For i = 1 To 5
                Set pr = r.CostRateTables(i).PayRates
                pr(1).StandardRate = 0
                pr(1).OvertimeRate = 0
                pr(1).CostPerUse = 0
                If pr.Count > 1 Then
                    For j = pr.Count To 2 Step -1
                        pr(j).Delete
                    Next j
                End If
            Next i
        End If
    Next r
    End Sub


    DJ Johnson

    Monday, December 3, 2012 5:08 PM