none
MS Project, VBA, Assignments and Custom Fields RRS feed

  • Question

  • Hello to everyone,

    I'm using Project 2010. In the projects of my company, we use some enterprise custom fields, and these fields have their values updated by the resources via PWA. The problem is that when these fields are updated through PWA, it appears that the values are stored in the Assignments, not the task itself.

    So, I wrote a code to get these values from the screen, do some math with them and update them to the task. For sure, the code is very slow for big projects and very fond of errors.

    My real question is: Is there a way to get these custom field values from assignments like we get from tasks, like the method .GetField?

    Tuesday, February 21, 2017 6:04 PM

All replies

  • Ruy,

    You say the code you wrote gets the values from the screen. That sounds like your code is running using foreground processing, which operates on objects in the current view, versus background processing, which operates directly on objects in Project's database, independent of the view. Background processing runs faster than foreground processing.

    Since your are in a Project Server environment, there may be ways to transfer the data directly through server queries but unfortunately I don't use Project Server so I can't guide you further on that. Maybe someone else will jump in with a suggestion.

    Meanwhile, you might want to take a look at the following Wiki article. It has code examples for transferring data from one object type (e.g. assignment) to another (e.g. task).

    https://social.technet.microsoft.com/wiki/contents/articles/32051.ms-project-extra-fields-in-views.aspx

    John

    Tuesday, February 21, 2017 8:03 PM
  • Hi john, thank you for the answer.

    Actually, I'm not using PS at all for coding. I use only Project's VBA Editor, since I don't have access to PS neither I know how to code for it.

    Indeed, the link you sent me is related to the problem. I screenshot the screen so it can be better visualized.

    [ms didn't allow me to post image, see imgur.com /a /xDFFg]

    The blue column is an enterprise field, and the red lines are resources, and the black line is the task. The "Pesos" field is updated via PWA by the resources and the value is shown in the 2nd /3rd line. Let's say that the task value of this column is the sum of the values provided by the resources, so i'm trying to make a routine to this like:

    Tasks(k).SetField(Field_ID, Assignments(m).GetField(Field_ID)+Assignments(n).GetField(Field_ID))

    The code is wrong of course but is just for show. The problem is that i don't find any way i can get these resource's field values if not by foreground processing.

    Tuesday, February 21, 2017 8:45 PM
  • Ruy,

    Sorry I don't know how to use imgur.com so I can't see the image and I don't wish to sign up.

    Are you saying you only have the Project application itself? Is it Project Pro or Project standard? How do you access the file?

    John

    Tuesday, February 21, 2017 9:59 PM
  • Hi John,

    I use Project Professional only. The file is stored in our PS, but the VBA code is written on the enterprise model of Project. All the processing is done locally, no by the server.

    For imgur, it's a website to store images, to access the link you just have to remove the white spaces in between, no signup.

    Wednesday, February 22, 2017 1:00 PM
  • Sorry to jump in here John but please assume that everything is done on the client with the usual VBA - project server has no impact on the VBA.  So if you can help Ruy with getting VBA to run in the background  to take info from Assignments into tasks I think we can solve his problem.

    Ben Howard [MVP] | web | blog | book

    Wednesday, February 22, 2017 2:36 PM
    Moderator
  • Ben,

    I was hoping that was the case. I just need to get a little more info from Ruy.

    John

    Wednesday, February 22, 2017 3:35 PM
  • Ruy,

    Based on Ben's statement may I assume you can open the file on Project Pro? And if that is the case then the code in the Wiki article, with a few modifications to pick up the desired enterprise fields, should get what you need. If not, please explain what it does not do and I'll modify it for you.

    John

    Wednesday, February 22, 2017 3:40 PM
  • John and Ben, Thank you for the answers.

    Yes, I open the file in Project Pro, and use VBA.

    The problem with the code in the link is that our fields are not Text1 nor Number1, Date1, etc. It's an enterprise field. When we are using an enterprise field, we use the method .GetField(), not .Number1, .Text1, etc. So, if I want a value from an enterprise field from a task, I use the code:

    Peso = task.GetField(F_Peso)

    where F_Peso is the Constant representing the enterprise field that is returned by the method Application.FieldNameToFieldConstant.

    Unfortunatelly, There is no version of this call when it comes to Assignments. It doesn't exists Assign.GetField(F_Peso). To get these values from the assignments, today, I use the cells that are shown on screen, which is very slow.

    My hopes are that I'd be able to get these values from within an object like Tasks() or Resources(), not from the screen.

    Thank you for all the support, sorry for my english.

    Wednesday, February 22, 2017 5:52 PM
  • Ruy,

    Okay, let's see if this works. I don't know which field is represented by F_Peso but as an example let's assume it is the EnterpriseCost1 field. The view you showed in the image you posted appears to be the Task Usage view. The following code will loop through each task's assignments, read and sum up the EnterpriseCost1 field and then write that value to the Task EnterpriseCost1 field.

    Does that help?

    And don't worry about your English, it's a whole lot better than if I were to try and write in Portuguese.

    John

    Sub xferAssEnterToTaskEnter()
    Dim t As Task
    Dim a As Assignment
    Dim temp As Single
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            For Each a In t.Assignments
                temp = temp + a.EnterpriseCost1
            Next a
            t.EnterpriseCost1 = temp
        End If
    Next t
    End Sub

    Wednesday, February 22, 2017 9:21 PM
  • Thank you John!

    At First I didn't understarnd how would it work but I did the test and it was successful! I sill don't get it, since EnterpriseCost1 (or other enterprise field) can be called like that since it is not a built-in object...

    Anyway, I apreciate your patience with my question I thank you for the answer!

    Thursday, February 23, 2017 5:12 PM
  • Ruy,

    You're welcome and thanks for the feedback.

    I confess, that I went down the GetField and SetField Methods before I backed up and took a fresh look. Fortunately I have the Pro version so I can run tests with enterprise fields even though I can't see or display them, other then in the Immediate Window. Even though enterprise fields do not appear in the object browser, they do in fact exist and can be used.

    Sometimes the simple direct approach is the best.

    John

    Thursday, February 23, 2017 5:54 PM