none
How do you change resource TSV value using VBA? RRS feed

  • Question

  • I've got a problem in Project 2010 that is driving me nuts, and I'd really appreciate any help people can provide.

    I have a Project file that I'm using to relate changes in task funding to changes in completion dates of related milestones.  For example, if funding for a task gets delayed 2 years, then the task completion gets delayed 2 years, and related milestone(s) may be impacted.  The budget and task completion dates are trivial to do in Excel, but we are using Project in order to capture complex many-to-many dependencies between the funded tasks and their associated milestones.  Each task has a single corresponding resource entry with multi-year budget (in the details section) entered as material (in units of $M).  That much works as planned.

    Since the financial analysts find the Resource Usage view cumbersome to use, I am developing a utility that will export current budget (funding) from Project to Excel, allow for budget edits within Excel, and import the edited budgets from Excel into Project.

    I can export resource data (names, text fields, etc.) and budgets (TimeScaleValues) from Project to Excel with no problem.  The editing portion is getting to where it needs to be.  But I can't figure out how to import the Excel data to Project.  I can edit the resource name and text fields, but not the funding values (details).

    The difficulty boils down to assigning any TSV (TimeScaleValue) object through VBA.  In an attempt to assign any TSV value, I wrote the following code:

    Sub TryThis()

        Dim tsvs as TimeScaleValues

        Set tsvs = ActiveProject.Resources(1).TimeScaleData(StartDate:=#10/1/2006#, EndDate:=#9/30/2049#, _

          Type:=pjResourceTimescaledWork, Timescaleunit:=pjTimescaleYears, Count:=1)

        tsvs(1).Value = 100

    End Sub

    I expected this code to assign the first resource's first year's entry to 100.  Instead the code fails on the "tsvs(1).Value = 100" line, delivering the error message:

    Run-time error '1101; -- The argument value is not valid.

    Does anyone have any thought of what I'm doing wrong?  How can I get this to work?

    Kindly regards,

    Tamon Honda

    Tuesday, April 24, 2012 9:12 PM

Answers

  • Oh and of course change your Type to pjAssignmentTimescaledWork.

    Also, Im assuming that you had just one value assignment (tsvs(1).Value = 100) because this was a test.

    You would want to have a loop that went through each of the values since your object set line has like 43 years. The code as you wrote it would assign the 500 value to only the first year and ignore the other 42 years.

    That said Im guessing that you would likely want the object assignment to match the span of the task itself. So something like this:

    Sub TryThis()
     
        Dim TSVS As TimeScaleValues
        Dim TSV As TimeScaleValue
        Set TSVS = ActiveProject.Resources(1).Assignments(1).TimeScaleData(StartDate:=ActiveProject.Resources(1).Assignments(1).Start, EndDate:=ActiveProject.Resources(1).Assignments(1).Finish, _
        Type:=pjAssignmentTimescaledWork, Timescaleunit:=pjTimescaleYears)
     
        For Each TSV In TSVS
            TSV.Value = 500
        Next TSV
    End Sub


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by Tamon Honda Friday, April 27, 2012 9:05 PM
    Tuesday, April 24, 2012 9:38 PM
    Moderator

All replies

  • Try going this for an assignment instead of for a resource. You cannot edit TSV at the resource level. use the same syntax you have above but instead of Resource(1) do it for a specific Assignment. If you are saying that each resource only EVER has one assignment then this should work:

    Set TSVS = Activeproject.Resources(1).Assignments(1).TimeScaledData......

    If a resource could have more than one assignment but any given task only has one assignment then:

    Set TSVS = Activeproject.Tasks(1).Assignments(1).TimeScaledData...

    If a task\resource could have more than one assignment then you will need to write code that lets you specify down to the assignment level.


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Tuesday, April 24, 2012 9:19 PM
    Moderator
  • Oh and of course change your Type to pjAssignmentTimescaledWork.

    Also, Im assuming that you had just one value assignment (tsvs(1).Value = 100) because this was a test.

    You would want to have a loop that went through each of the values since your object set line has like 43 years. The code as you wrote it would assign the 500 value to only the first year and ignore the other 42 years.

    That said Im guessing that you would likely want the object assignment to match the span of the task itself. So something like this:

    Sub TryThis()
     
        Dim TSVS As TimeScaleValues
        Dim TSV As TimeScaleValue
        Set TSVS = ActiveProject.Resources(1).Assignments(1).TimeScaleData(StartDate:=ActiveProject.Resources(1).Assignments(1).Start, EndDate:=ActiveProject.Resources(1).Assignments(1).Finish, _
        Type:=pjAssignmentTimescaledWork, Timescaleunit:=pjTimescaleYears)
     
        For Each TSV In TSVS
            TSV.Value = 500
        Next TSV
    End Sub


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by Tamon Honda Friday, April 27, 2012 9:05 PM
    Tuesday, April 24, 2012 9:38 PM
    Moderator
  • To backup Brian's good advice (welcome back to MVPdom Brian!) see the chapter on Timescaled data from my VBA book duplicated in MSDN:

    http://msdn.microsoft.com/en-us/library/ee355231(office.12).aspx

    It's for Project 2007 but works the same in 2010. Yes you can only write timescaled data to assignments, the same as in any Usage View.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Wednesday, April 25, 2012 12:00 AM
    Moderator
  • It works!!  Thanks very, very much Brian & Rod!!

    Please see my boss for your reward!


    Tamon Honda

    Friday, April 27, 2012 9:08 PM