Assigning Work to tasks from Excel using VBA RRS feed

  • Question

  • I am assigning hours from a spreadsheet into the work column of the resource for each task.  However, some hours are inserted correctly and others aren't.  In each case where the hours are incorrect, the number of hours inserted is less than the spreadsheet.  I already checked to make sure that the resources were not over their maximum units.  Here is the function I am using to assign the resources and hours to the project file.  I am using Project and Excel 2007.

    Function WriteToMasterTemplateProjectManagement(sResource, hrs, currow) As Boolean


        Dim t As Task

        Dim ts As Tasks

        Dim a As Assignment

        Set ts = ActiveProject.Tasks

        Dim prow As Long


        'map the currow of spreadsheet to the corresponding row of project

        Select Case currow

            Case 17

                prow = 5

            Case 18

                prow = 6

            Case 23

                prow = 8

            Case 24

                prow = 9

            Case 25

                prow = 10

            Case 26

                prow = 11

            Case 49

                prow = 13

            Case Else

                MsgBox "There was an error processing your row. " & currow

                WriteToMasterTemplate = False

                Exit Function

        End Select


        'Check that the resource is defined within the project

        Set t = ts(prow)

        'the resource and number of hours come from Excel

        'need to lookup the resource id from resourcepool

        'given the resource name sResource

        Dim R As Long


        For R = 1 To ActiveProject.Resources.Count

            If ActiveProject.Resources(R).Name = sResource Then

               RID = ActiveProject.Resources(R).ID

               Debug.Print sResource & " rid " & RID

            End If

        Next R


        lassignuid = ActiveProject.Tasks(prow).Assignments.Add(ResourceID:=RID)

        'Based on assignment unique ID (UID) returned while adding

        'resource, we can just assign the hours we have from the spreadsheet

    ‘this is where it inserts hours

        ActiveProject.Tasks(prow).Assignments.UniqueID(lassignuid).Work = (hrs * 60)


        WriteToMasterTemplateProjectManagement = True

    End Function

    Thanks for any help in advance!


    Wednesday, August 25, 2010 6:51 PM


All replies

  • Hi,

    This will always work fine... as long as you don't have more than one new assignment per task.

    But when you add the new assignment, if the task is fixed work or effort driven, it will spread the work over all resources including the new one, so you get a lower work value for the resoruce added first.

    After Next R add:



    Wednesday, August 25, 2010 7:54 PM
  • In addition to Jan's good advice, try simplifying your code:

    Sub test()
    Dim Tsk As Task
    Dim Res As Resource
    Dim Assgn As Assignment
        Set Tsk = ActiveProject.Tasks(1)
        Set Res = ActiveProject.Resources("Res")
        Set Assgn = Tsk.Assignments.Add(Tsk.ID, Res.ID)
    End Sub

    You need to add some error checking (for example if resource Res does not exist, but teh above code is much simpler.

    The one and only Project VBA Book Rod Gill Project Management
    Thursday, August 26, 2010 8:45 AM