Thursday, December 16, 2010

Microsoft Project: TimeScaleData Method from .NET without GC.Collect

Project has a bug where accessing time scaled values causes an exception after a few calls.  You can get around it by calling GC.Collect() and GC.WaitForPendingFinalizers() but this is super slow.  Here is an example of what that looks like.

   1: TimeScaleValues workValues = assignment.TimeScaleData(startDate, endDate, pjAssignmentTimescaleDataType, PjTimescaleUnit.pjTimescaleDays, 1);
   2:  
   3: for (int i = 1; i <= days; i++)
   4: {
   5:     try
   6:     {
   7:         string valueData = workValues[i].Value.ToString();
   8:  
   9:         if (string.IsNullOrEmpty(valueData))
  10:         {
  11:             continue;
  12:         }
  13:  
  14:         totalTSV = totalTSV + decimal.Parse(valueData);
  15:  
  16:     }
  17:     catch
  18:     {
  19:         Marshal.FinalReleaseComObject(workValues);
  20:         GC.Collect();
  21:         GC.WaitForPendingFinalizers();
  22:  
  23:         i = 1;
  24:         totalTSV = 0;
  25:  
  26:         workValues = assignment.TimeScaleData(startDate, endDate, pjAssignmentTimescaleDataType, PjTimescaleUnit.pjTimescaleDays, 1);
  27:     }
  28: }

 

Here is a glorious hack that involves injecting a temporary macro into the Global.MPT and then calling the macro via reflection to have the TimescaleData method be called inside Project to avoid the problem.

First, inject this macro:

Public Sub GetAssignmentTSV(ByVal projectName As String, ByVal taskIndex As Long, ByVal assignmentIndex As Long, ByVal startDate As String, ByVal endDate As String, ByVal tsvType As Long, byref totalTSV As Variant)

    Dim tsv As TimeScaleValues

    Set tsv = Application.Projects(projectName).Tasks(taskIndex).Assignments(assignmentIndex).TimeScaleData(startDate, endDate, tsvType , pjTimescaleDays)
    
    Dim t As TimeScaleValue
    For Each t In tsv
        
        totalTSV = totalTSV + Val(t.Value)
    
    Next
            
End Sub

By doing this (it attempts to clean up old version of the macro first):

   1: private void InjectTSVMacro()
   2:         {
   3:             try
   4:             {
   5:                 List<VBComponent> removeComponents = new List<VBComponent>();
   6:  
   7:                 foreach (VBComponent component in _Application.VBE.VBProjects.Item(1).VBComponents)
   8:                 {
   9:                     int startLine = 0;
  10:                     int startColumn = 0;
  11:                     int endLine = 0;
  12:                     int endColumn = 0;
  13:  
  14:                     if (component.CodeModule.Find("GetAssignmentTSV", ref startLine, ref startColumn, ref endLine, ref endColumn, false, false, false))
  15:                     {
  16:                         removeComponents.Add(component);
  17:                     }
  18:                 }
  19:  
  20:                 foreach (VBComponent t in removeComponents)
  21:                 {
  22:                     _Application.VBE.VBProjects.Item(1).VBComponents.Remove(t);
  23:                 }
  24:  
  25:                 _VBAModule = _Application.VBE.VBProjects.Item(1).VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
  26:  
  27:                 _VBAModule.CodeModule.AddFromString(Resources.TSVMacro);
  28:  
  29:                 _GetTSVMethod = TSVMethod.MacroInjection;
  30:             }
  31:             catch (Exception exception)
  32:             {
  33:                 QualityFx.WriteException(exception);
  34:                 _GetTSVMethod = TSVMethod.ObjectModel;
  35:             }
  36:         }

 

Then call the macro like this:

   1: return RunTSVMacro(_Application,
   2:          new object[]
   3:              {
   4:                  "GetAssignmentTSV",
   5:                  syncParameters.Project.Name,
   6:                  task.ID,
   7:                  assignmentId,
   8:                  startDate.ToString(),
   9:                  endDate.ToString(),
  10:                  (int) pjAssignmentTimescaleDataType,
  11:                  totalTSV
  12:              });

 

RunTSVMacro looks like this:

   1: public decimal RunTSVMacro(Application application, object[] args)
   2: {
   3:     ParameterModifier parameterModifier = new ParameterModifier(TSV_MACRO_NUMBER_OF_ARGS);
   4:  
   5:     parameterModifier[TSV_MACRO_OUT_ARG] = true;
   6:  
   7:     ParameterModifier[] parameterModifiers = { parameterModifier };
   8:  
   9:     application.GetType().InvokeMember("Run",
  10:                                        BindingFlags.Default | BindingFlags.InvokeMethod,
  11:                                        null,
  12:                                        application,
  13:                                        args,
  14:                                        parameterModifiers,
  15:                                        null,
  16:                                        null);
  17:  
  18:     return (decimal)args[TSV_MACRO_OUT_ARG];
  19: }

The two constants are:

   1: private const int TSV_MACRO_NUMBER_OF_ARGS = 8;
   2: private const int TSV_MACRO_OUT_ARG = 7;

The RunTSVMacro method has to use the parameter modifier to let reflection know that the 7 argument is an out argument because you can call a VBA function using Project’s Run method.

Hope this helps.

2 comments:

Johannes said...

Dear Colby,

even though this post is quite old, it still helped me a lot. I only saw it today looking for a solution for the Project .NET PIA slowness concerning timescale data and this is a cool way around it. Let's hope that Microsoft will keep this door open for some years to come.
Thank you so much for posting this!

Best regards from Germany,

Joe

Colby Africa said...

You are welcome. I am sorry it took me so long to respond. I haven't been very active on the blog for a while.

Disclaimer

Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.