CODEWITHSUNDEEP
excelvb.netvisual-studiovstoproject
Jhorbam
Jhorbam

Reputation: 23

Importing Task Usage view data to Excel

I'm developing a code that allows me to access the TASK USAGE view of any Ms Project file, and to be able to import that information to an Excel file.

I'm using VB .NET and VSTO, accessing through the Microsoft.Office.Interop.MSProject library.

So far, the export to Excel of the WBS data, Task Name, Work, Duration, Start and End for each element seems to work well. The problem arises when I want to bring to Excel the information of the scheduled work distributed week by week for a task during its life cycle.

For this I have defined my PjTaskTimescaledDataType and its constants

Public Enum PjTaskTimescaledDataType
pjTaskTimescaledWork = 0
pjTaskActualWork = 2
pjTaskTimescaledBudgetWork = 1172
pjTaskTimescaledBaselineWork = 1
pjTaskTimescaledBaselineBudgetWork = 1117 End Enum

But none of them gives me the data as it is presented by Ms Project in its TASK USAGE view, even knowing that the data obtained is in minutes I divide it by 60 to convert it to hours and still they do not match.

When the EDT data is printed, Task Name, Work, Duration, Start and End. Work presents a value x for activity N, and the sum of the columns of the scheduled work distributed week by week differs from the value printed in Column C for this case which would be WORK.

Does anyone know what constant I should use in PjTaskTimescaledDataType to be able to obtain the information on the scheduled work distributed week by week for a task during its life cycle and so that its sum matches the value of the WORK or PLANNED WORK field?

Thanks

Upvotes: 1

Views: 39

Answers (1)

Emad Kerhily
Emad Kerhily

Reputation: 280

Use the TimeScaleData method on the Task object with:

  • Start and End Dates: Use the task’s Start and Finish properties.

  • Timescale Unit: PjTimescaleUnit.pjTimescaleWeeks to group by weeks. Also, take into consideration that MS Project stores work in minutes. Convert to hours using floating-point division (i.e. /60), not integer division, to preserve decimal precision.

     ' Set up headers in Excel
 worksheet.Cells(1, 1).Value = "Task Name"
 worksheet.Cells(1, 2).Value = "Work (Hours)"
 worksheet.Cells(1, 3).Value = "Start Date"
 worksheet.Cells(1, 4).Value = "End Date"
 Dim colIndex As Integer = 5 ' Start weekly data from column E
 For Each task As MSProject.Task In project.Tasks
     If Not task Is Nothing Then
         ' Write basic task info to Excel
         worksheet.Cells(rowIndex, 1).Value = task.Name
         worksheet.Cells(rowIndex, 2).Value = task.Work / 60 ' Convert minutes to hours
         worksheet.Cells(rowIndex, 3).Value = task.Start
         worksheet.Cells(rowIndex, 4).Value = task.Finish

         ' Get weekly timescaled work
         Dim tsData As MSProject.TimeScaleValues = task.TimeScaleData(
             task.Start, task.Finish,
             MSProject.PjTaskTimescaledDataType.pjTaskTimescaledWork,
             MSProject.PjTimescaleUnit.pjTimescaleWeeks, 1)

         ' Reset column index for weekly data
         colIndex = 5

         For Each tsValue As MSProject.TimeScaleValue In tsData
             ' Convert minutes to hours (use Double for precision)
             Dim weeklyWork As Double = tsValue.Value / 60
             worksheet.Cells(rowIndex, colIndex).Value = weeklyWork
             colIndex += 1
         Next

         rowIndex += 1
     End If
 Next

Upvotes: 1

Related Questions