Tuesday, 24 April 2012 9:12 PM
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:
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
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?
Tuesday, 24 April 2012 9:19 PMModerator
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.
Tuesday, 24 April 2012 9:38 PMModerator
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
- Marked As Answer by Tamon Honda Friday, 27 April 2012 9:05 PM
Wednesday, 25 April 2012 12:00 AMModerator
To backup Brian's good advice (welcome back to MVPdom Brian!) see the chapter on Timescaled data from my VBA book duplicated in MSDN:
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 GillThe one and only Project VBA Book Rod Gill Project Management
Friday, 27 April 2012 9:08 PM
It works!! Thanks very, very much Brian & Rod!!
Please see my boss for your reward!