How to skip off date in TimeScaleData (read mpp file)?

Question

I want to display the work hours of all days from 29/5 to 30/6 but in the interval. It contains Saturdays and Sundays (non-working days). I use TimeScaleData but it auto-stops when it sees a non-working day. I try to add(1) to the finish day but it still stops, so I can't display the work hours correctly.

Microsoft.Office.Interop.MSProject.Application app = new Microsoft.Office.Interop.MSProject.Application();
app.FileOpenEx(Path, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    PjPoolOpen.pjPoolReadWrite, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.MSProject.Project pj = app.ActiveProject;
foreach(Resource re in pj.Resources)
{
    foreach (Assignment assign in re.Assignments)
    {
        double h = 0;
        var tsvs = assign.TimeScaleData("5/29/2017", "7/1/2017", MSProject.PjAssignmentTimescaledData.pjAssignmentTimescaledWork, MSProject.PjTimescaleUnit.pjTimescaleDays, 1);
        try
        {
            foreach (TimeScaleValue tsv in tsvs)
            {
                for (int i = 0; i < totalday ; i++)
                {
                    h += Convert.ToDouble(tsv.Value); //sum all value
                }     
            }

        }
        catch { }
    }
}

Rachel Hettinger · Answer 1 · 2017-10-11T17:52:58.470

0

Your code encounters a run-time error on this line: h += tsv.Value because tsv.Value is an empty string for days there is no work. Check the datatype of tsv.Value before trying to add.

MSDN Documentation—note that the Value property returns a Variant, not a double.

edited Oct 11 '17 at 17:52

answered Oct 10 '17 at 05:24

Rachel Hettinger

7,927
2
21
31

it works. Bugs and bugs, i feel stress :v :v Thanks so much Rachel. – gggg Oct 11 '17 at 11:11
Hi @Rachel, I research and detect funtion Val in VBA. That post said:""tsv.Value returns an empty string ("") for time slices outside the object's date range. Val(tsv.Value) converts the empty string to a zero which is much more useful"".I understood Val() like Convert.ToDouble in C# but when i use it sees error same above. – gggg Oct 12 '17 at 11:02
Check the datatype of `tsv.Value` before using it. See [this SO post for type checking in c#](https://stackoverflow.com/questions/983030/type-checking-typeof-gettype-or-is). – Rachel Hettinger Oct 12 '17 at 14:35
Finally, i looking for my issue. I added GC.Collect(). Thanks so much Rachel. – gggg Oct 14 '17 at 04:04
I try to access timescaledata but it's slowly(results 600 rows but it runs > 3'). I used Application.Sort(Resource Name, Start, Unique ID) but still doesn't work. Can you explain help me??Thanks Rachel – gggg Oct 16 '17 at 08:42
1) Remove your inner loop `for (int i = 0; i < totalday ; i++)`. At best it is unnecessary, at worst you are adding the same `tsv.Value` multiple times. 2) Check the data type of` tsv.Value` before trying to add it (rather than forcing all values to be converted). 3) Sort is irrelevant here. – Rachel Hettinger Oct 16 '17 at 17:12
FYI: The com exception that `GC.Collect` solved was fixed in Project 2010. – Rachel Hettinger Oct 16 '17 at 17:12
Really, I use Project 2007 and it has a problem with GC.Collect. Yap, I think sort data will group clusters with same start day , I'm so bad :( :( . – gggg Oct 16 '17 at 18:04
I removed loop for. @Rachel, I don't understand clearly about check the data type of tsv.Value, I follwed your link but i'm feel vague. – gggg Oct 16 '17 at 18:09

Eric Christoph · Answer 2 · 2017-10-18T12:07:08.270

In my code tsv.value returned an empty string when iterating through the timescalevalues. Checking for IsNumeric worked:

Private Sub ProcessAssignments(T As Task)

    Dim A As Assignment
    Dim tsvsHours As TimeScaleValues
    Dim tsvsCosts0 As TimeScaleValues
    Dim dblWork As Double
    Dim curCostClassA As Double


    ' Process assignments
    For Each A In T.Assignments
        ' Get the timescale collection objects for Hours and Costs
        tsvsHours = A.TimeScaleData(
            StartDate:=T.BaselineStart,
            EndDate:=T.BaselineFinish,
            Type:=PjAssignmentTimescaledData.pjAssignmentTimescaledBaselineWork,
            TimeScaleUnit:=PjTimescaleUnit.pjTimescaleMonths,
            Count:=1)

        tsvsCosts0 = A.TimeScaleData(
            StartDate:=T.BaselneStart,
            EndDate:=T.BaselineFinish,
            Type:=PjAssignmentTimescaledData.pjAssignmentTimescaledBaselineCost,
            TimeScaleUnit:=PjTimescaleUnit.pjTimescaleMonths,
            Count:=1)

        ' Iterate through the assignment timescalevalues
        For i As Integer = 1 To tsvsCosts0.Count
            If IsNumeric(tsvsCosts0(i).Value) = True Then 'Cannot process non-working times with an empty string value

                ' Get the hours from the tsvsHours collection
                If IsNumeric(tsvsHours(i).Value) Then
                    dblWork = CDbl(tsvsHours(i).Value / 60)
                Else
                    dblWork = 0
                End If

                ' Get the costs from the Baseline collection
                If IsNumeric(tsvsCosts0(i).Value) Then
                    curCostClassA = tsvsCosts0(i).Value
                Else
                    curCostClassA = 0
                End If


                ' Do stuff here

            End If
        Next i

        tsvsHours = Nothing
        tsvsCosts0 = Nothing

    Next A
End Sub

I checked but i don't know it works incoorect. Example, 2 rows have duplicate start,finish day and day works. But row1 is right reult but no2 incorrect. — gggg, Oct 12 '17 at 09:49
I updated the example to provide a fully working sub. Maybe something was off in the timescalevalues collection? — Eric Christoph, Oct 13 '17 at 11:42
Yah, Thanks Eric@@ Due to code reading more than 9 time slices. I added GC.Collect() and it's fine. — gggg, Oct 14 '17 at 04:10
FYI: If you are using the TimeScaleData method to get baseline units (e.g. BaselineWork), you should use the corresponding baseline dates as the start and finish points (not forecast start and finish). — Rachel Hettinger, Oct 16 '17 at 17:07
@RachelHettinger That is usually true; example revised to reflect baseline start and finish. — Eric Christoph, Oct 18 '17 at 12:08

How to skip off date in TimeScaleData (read mpp file)?

2 Answers2