TimeScaleData in Project using .net

Question

I need to get timescaledata from a Ms Project mpp file by custom date ranges that match our accounting periods. I'm writing this in .net and using the interops. I'm running Ms Project 2010.

here is a sample for the concept:

Private Sub Process()
    Dim start As Date = Date.Now

    If txtImportFile.Text = "" Then
        Exit Sub
    End If

    Dim pjApplication As Microsoft.Office.Interop.MSProject.Application

    pjApplication = New Microsoft.Office.Interop.MSProject.Application
    pjApplication.Visible = False

    pjApplication.FileOpenEx(Name:=txtImportFile.Text, ReadOnly:=True)

    Dim project As Microsoft.Office.Interop.MSProject.Project = Nothing
    If pjApplication.Projects.Count = 0 Then
        pjApplication.FileExit(PjSaveType.pjDoNotSave)
        Exit Sub
    Else
        project = pjApplication.Projects(1)
    End If

    Dim res As Resource = Nothing
    Dim bLabor As Boolean = False
    Dim dTimeScaleValue As Decimal = 0
    Dim dPeriodValue As Decimal = 0

    Dim timeScaleVals As TimeScaleValues = Nothing
    Dim timeScaleValActuals As TimeScaleValues = Nothing

    Dim tskTimeScaleVals As TimeScaleValues = Nothing

    For Each tsk As Task In project.Tasks
        For Each a As Assignment In tsk.Assignments
            res = project.Resources(a.ResourceID)

            If res.Type = PjResourceTypes.pjResourceTypeWork Then
                bLabor = True
            Else
                bLabor = False
            End If


            If bLabor Then
                timeScaleVals = a.TimeScaleData(StartDate:=a.Start, EndDate:=a.Finish,
                                                Type:=PjAssignmentTimescaledData.pjAssignmentTimescaledWork, TimeScaleUnit:=PjTimescaleUnit.pjTimescaleDays,
                                                Count:=1)
            Else
                timeScaleVals = a.TimeScaleData(StartDate:=a.Start, EndDate:=a.Finish,
                                                Type:=PjAssignmentTimescaledData.pjAssignmentTimescaledCost, TimeScaleUnit:=PjTimescaleUnit.pjTimescaleDays,
                                                Count:=1)
            End If

            If bLabor Then
                timeScaleValActuals = a.TimeScaleData(StartDate:=a.Start, EndDate:=a.Finish,
                                                      Type:=PjAssignmentTimescaledData.pjAssignmentTimescaledActualWork, TimeScaleUnit:=PjTimescaleUnit.pjTimescaleDays,
                                                      Count:=1)
            Else
                timeScaleValActuals = a.TimeScaleData(StartDate:=a.Start, EndDate:=a.Finish,
                                                      Type:=PjAssignmentTimescaledData.pjAssignmentTimescaledActualCost, TimeScaleUnit:=PjTimescaleUnit.pjTimescaleDays,
                                                      Count:=1)
            End If


            dPeriodValue = 0
            dTimeScaleValue = 0

            For idx = 1 To timeScaleVals.Count
                If timeScaleVals(idx).Value.ToString.Trim = "" Then
                    dTimeScaleValue = 0
                Else
                    dTimeScaleValue = timeScaleVals(idx).Value
                End If

                If Not IsNothing(timeScaleValActuals) Then
                    If Not IsNothing(timeScaleValActuals(idx)) AndAlso timeScaleValActuals(idx).Value.ToString.Trim <> "" Then
                        dTimeScaleValue -= timeScaleValActuals(idx).Value
                    End If
                End If

                If bLabor Then
                    dPeriodValue += Decimal.Round(dTimeScaleValue / 60D, 2)
                Else
                    dPeriodValue += dTimeScaleValue
                End If
            Next
        Next
    Next


    pjApplication.FileExit(PjSaveType.pjDoNotSave)

    MsgBox(DateDiff(DateInterval.Second, start, Date.Now))
End Sub

I would expect the call to TimeScaleData to return a single TimeScaleValues object because I'm setting the unit to days and the count to the number of days in the range I'm sending. But, I end up getting back more than one record and if I look at the data from those records it goes beyond the end date I passed in and so if I try aggregating the value from the multiples it rreturns, it's not the right value for the time period I'm trying to look at.

Because of this I end having to loop through the time scale data with the count set to 1 and go day by day which on a 1.1mb project I'm working with takes 25min just to loop through all the tasks, assignments and then timescaledata.

What am I not understanding about retrieving the timescaledata? Is there any way to achieve what I'm trying to achieve more efficiently?

Answer 1

The TimeScaleData method returns a TimeScaleValues object which is a collection of TimeScaleValue objects. You do need to loop through these daily values and sum them up. However, this is fast, even on large projects.

Sub GetWork()

Dim dteStart As Date = "3/30/19 00:00:00"
Dim dteEnd As Date = "4/27/19 00:00:00"

For Each tsk As Task In proj.Tasks
    For Each a As Assignment In tsk.Assignments

        Dim timeScaleVals As TimeScaleValues
        timeScaleVals = a.TimeScaleData(StartDate:=dteStart,
                                        EndDate:=dteEnd,
                                        Type:=PjAssignmentTimescaledData.pjAssignmentTimescaledWork,
                                        TimeScaleUnit:=PjTimescaleUnit.pjTimescaleDays)
        Dim WorkMinutes As Int32
        For Each tsv As TimeScaleValue In timeScaleVals
            WorkMinutes += Val(tsv.Value)
        Next tsv
        ' do something with WorkMinutes
    Next
Next

End Sub

No need to bother with the Count argument with the TimeScaleData method since the start and end dates are supplied. Be sure to use the correct end date--4/27/19 is the same as 4/27/19 12:00 AM, so no work on that day will be counted; use 4/28/19 if you need 4/27/19 values. And lastly, convert the timescale values to hours by dividing by 60 to get hours.