Quicker alternative to TimeScaleData for updating work in assignment for a specific time period

user_4244564 0
Viewed 53

I'm using VBA to update actual work in my projects. From an external csv sheet, I get actual work per task, resource and week, which then needs to be fed into Project in the same three dimensions.

I find that many things can be coded in two very different ways in Project, depending on whether I get the inspiration from recording a macro, or from exploring methods and properties in the Object Browser. For this operation, I haven't found any other way than what I learned by recording a macro while I update actual work in the Time Scale window in Task Usage view. This has given me the TimeScaleData method.

Here is a simplified version of my code. The variables MyStartDateString, MyEndDateString and MyActualWork are defined elsewhere.

dim t as Task
dim a as Assignment    
For Each t in ActiveProject.Tasks
     For Each a in t.Assignments
          a.TimeScaleData(StartDate:=MyStartDateString, _
          EndDate:=MyEndDateString, Type:=10, TimeScaleUnit:=3, _ 
          Count:=1).Item(1).Value = MyActualWork
     Next a
Next t

There is actually a lot more going on before this part, where I step through each week in the csv file and match the name of the task and resource with those in Project so on, but this is the critical part of it. I have found that in a project with around 1000 of these TimeScaleData operations, the whole thing takes around 45 seconds, which is annoyingly slow. Is there a faster and more elegant way?

Thanks a lot for your help!

ms-project vba
1 Answers

Yes, there are often two ways of doing the same thing in Project, but in this case there is only one. However, it should help to make fewer calls to the TimeScaleData method--only one per assignment and then loop through the TimeScaleValues collection and set the Value property for each TimeScaleValue object.

Also, pass in date variables for the start and end dates, rather than strings. And turn off Calculation and ScreenUpdating.

Look at similar stack overflow posts such as TimeScaleData in Project using .net.

user_10566320
Related