none
Project Server 2010: Macro to Roll Custom Assignment Data to Task Rows RRS feed

Answers

  • Hey Brian,

    I am using the below code and it is giving me an "Object Required" error:

     

    Private Sub Project_Open(ByVal pj As Project)
    
    Sub Roller()
    
    Dim tsk As Task
    
    Dim asn As Assignment
    
    
    
    For Each tsk In Activeprojects.Tasks
    
      If Not (tsk Is Nothing) Then
    
        For Each asn In tsk.Assignments
    
          tsk.Adjust_Days = asn.Adjust_Days
    
        Next asn
    
      End If
    
    Next tsk
    
    
    
    End Sub

    My custom field is a text field named "Adjust Days."  I get the same error whether or not I include the underscore or not for the CUSTOMFIELDNAMEHERE.

    What am I doing wrong?

    Thanks,

    Bryan

     

    Thursday, July 14, 2011 5:45 PM

All replies

  • Explain a bit about what you are wanting to do specifically and what fields you want to move from assignment level to task level. then we can look at what the code might look like.
    --- Brian Kennemer Web: http://www.deltabahn.com Blog: http://www.projectified.com
    Wednesday, July 13, 2011 4:04 PM
    Moderator
  • 1) I know a couple of people have developed similar macros.
    2) You might have more luck in the Project Server DEV forum.
    3) I forget, but an example may be found in Rod Gill's book, VBA Programming
    with Microsoft Project
     
     

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Wednesday, July 13, 2011 4:21 PM
    Moderator
  • Sure-

    I have a custom flag and a custom lookup field.  I have then both set to roll-down to assignment rows and they work as expected to submit the data from PWA to Project.

    The data stays at the assignment level and doesn't roll-up to the task level in project.

    I hope that's enough info.  If it isn't let me know and I will get whatever else is asked.

    Thanks Brian!

    Wednesday, July 13, 2011 4:24 PM
  • The difficult part about this kind of code is in handling what to do if there is more than one reosurce assigned to the task. If there is then they each get to put in values for these fields. They could all be different. Which one gets rolled up?

     

    In its most simple form the code looks like

     

    Sub Roller()
    Dim tsk As Task
    Dim asn As Assignment
    
    For Each tsk In Activeprojects.Tasks
      If Not (tsk Is Nothing) Then
        For Each asn In tsk.Assignments
          tsk.CUSTOMFIELDNAMEHERE = asn.CUSTOMFIELDNAMEHERE
        End If
      End If
    Next tsk
    
    End Sub
    

    This would work fine for tasks whre there was just one assignemnt. for tasks with more than one the last asignments value would be written into the task level field.

     


    --- Brian Kennemer Web: http://www.deltabahn.com Blog: http://www.projectified.com
    Wednesday, July 13, 2011 5:13 PM
    Moderator
  • So when I open VBA and copy the code, I add the custom name field and click run.  I get the following error:

    "Compile error: end if without block if."

    Additionally, how do you make this automatically run at file open?

    Thanks Brian.

    Wednesday, July 13, 2011 5:33 PM
  • Doh! LOL

    Take out that first end if.

     

    Should look like this:

     

    Sub Roller()
    Dim tsk As Task
    Dim asn As Assignment
    
    For Each tsk In Activeprojects.Tasks
      If Not (tsk Is Nothing) Then
        For Each asn In tsk.Assignments
          tsk.CUSTOMFIELDNAMEHERE = asn.CUSTOMFIELDNAMEHERE
      End If
    Next tsk
    
    End Sub

     

    As far as getting it to run on open i would test this in a development environment first but you can open the Enterprise global, open VBA, doubleclick on the ThisProject node in the project explorer on the left, for the checked out enterprise global and then in the drop down that says "General" at the top of the page pick "project". This will then show you the Open event. you could then paste the code below into that event. Some people like to have the routine above added to the global and then put a call to that routine in the open event but the result is the same.

    Make sure you test this in a non-production environment. This code has no error handling and is meant as a starting point example only.

     

    Dim tsk As Task
    Dim asn As Assignment
    
    For Each tsk In Activeprojects.Tasks
      If Not (tsk Is Nothing) Then
        For Each asn In tsk.Assignments
          tsk.CUSTOMFIELDNAMEHERE = asn.CUSTOMFIELDNAMEHERE
        End If
      End If
    Next tsk
    

     


    --- Brian Kennemer Web: http://www.deltabahn.com Blog: http://www.projectified.com
    Wednesday, July 13, 2011 6:15 PM
    Moderator
  • When inserting the code into the VBA under Project and Open I get the same error when I open a file.  Maybe I copied the code wrong?

     

    Private Sub Project_Open(ByVal pj As Project)
    Dim tsk As Task
    Dim asn As Assignment
    
    For Each tsk In Activeprojects.Tasks
     If Not (tsk Is Nothing) Then
      For Each asn In tsk.Assignments
       tsk.AdjustDays = asn.AdjustDays
     End If
    Next tsk
    
    End Sub

    Wednesday, July 13, 2011 7:20 PM
  • Wow. I am having a terrible day. :-)

    Please accept my apologies. That is what I get for trying to be in hurry. LOL

    The code below will work.

     

    Sub Roller()
    Dim tsk As Task
    Dim asn As Assignment
    
    For Each tsk In Activeprojects.Tasks
      If Not (tsk Is Nothing) Then
        For Each asn In tsk.Assignments
          tsk.CUSTOMFIELDNAMEHERE = asn.CUSTOMFIELDNAMEHERE
        Next asn
      End If
    Next tsk
    
    End Sub


    --- Brian Kennemer Web: http://www.deltabahn.com Blog: http://www.projectified.com
    Wednesday, July 13, 2011 9:08 PM
    Moderator
  • Hey Brian,

    I am using the below code and it is giving me an "Object Required" error:

     

    Private Sub Project_Open(ByVal pj As Project)
    
    Sub Roller()
    
    Dim tsk As Task
    
    Dim asn As Assignment
    
    
    
    For Each tsk In Activeprojects.Tasks
    
      If Not (tsk Is Nothing) Then
    
        For Each asn In tsk.Assignments
    
          tsk.Adjust_Days = asn.Adjust_Days
    
        Next asn
    
      End If
    
    Next tsk
    
    
    
    End Sub

    My custom field is a text field named "Adjust Days."  I get the same error whether or not I include the underscore or not for the CUSTOMFIELDNAMEHERE.

    What am I doing wrong?

    Thanks,

    Bryan

     

    Thursday, July 14, 2011 5:45 PM
  • If your field name has two words in it I dont think it will work. The task object allows for a method where you can lookup the field name. The assignment object does NOT have this. I would put an underscore in the field name itself so that the actual name of the field is Adjust_Days. then it will work for you if you call tsk.Adjust_Days = asn.Adjust_Days

     

    also, in the code above you have accidentally kept the "Sub Roller()" inside the Project_Open sub. I would delete that text.


    --- Brian Kennemer Web: http://www.deltabahn.com Blog: http://www.projectified.com
    Thursday, July 14, 2011 5:55 PM
    Moderator
  • Hi Brian and all,

    Are you sure? I thought you had to use

    FieldnametoFieldconstant

    Then getfield or setfield.

    Greetings,

     

    Thursday, July 14, 2011 7:27 PM
    Moderator
  • You can use that on Tasks and Resources but the Assignment object does not have Getfield or set field methods. the assignment object DOES let you call the name of the field directly (Assignment.FIELDNAME) but it only works if the field name is one word.
    --- Brian Kennemer Web: http://www.deltabahn.com Blog: http://www.projectified.com
    Thursday, July 14, 2011 7:30 PM
    Moderator
  • Hi,

    Mind the doubtful formulation of my previous reply... I couldn't believe you would be wrong :)

    Thanks for the lesson!

    Thursday, July 14, 2011 7:58 PM
    Moderator
  • Oh please! LOL

    Nobody bats 1000. :-)

    But thanks for the compliment!


    --- Brian Kennemer Web: http://www.deltabahn.com Blog: http://www.projectified.com
    Thursday, July 14, 2011 10:12 PM
    Moderator