Check out my new blog at https://shibumiware.blogspot.com

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.

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.