Project

The "Analyze Timescaled Data In Excel" add-in which ships with Microsoft Project has a couple of limitations, the first is that it is not easy to find, and the second is that it is task-based only, so if you want to export resource data you are out of luck. In this post I'll show how to write your own code to export resource timescaled data and use what you have learned to export almost any sort of timescaled data you can think of.

What is Timescaled Data?
Project is different from other applications like Excel because it has a dimension of time. Tasks have values for work and cost, but also contain the time dimension in the form of duration. On a specific task, the amount of work or cost may vary during the task instead of being spread evenly across the task. Because of this users sometimes need to view this information by day or week or hours. Within project they do this using the task usage view or the resource usage view and setting the timescale the way they want to see it. But sometimes they want this data outside of project and when they try and cut and paste, they find that it is not a simple matter. The view can not simply be copied and pasted. So to do this you need help from VBA.

Using VBA to Export Data
Underneath the surface of project is a powerful programming language called Visual Basic for Applications (VBA). It can be used to do just about everything that you can do manually in Microsoft Project but it can also do things which would be quite tedious or difficult to do manually. This example presumes you know have some understanding of VBA and if you don't I suggest you read the articles I've posted here (link to programming archive). VBA can open and operate other Microsoft Office applications. In this case we will open Excel and write data into it.

The TimeScaleData Method and the TimeScaleValues Collection
The TimeScaleData method is used to get a collection of timescaled values from tasks, assignments or resources which you can iterate through or read. The syntax for TimeScaleData method is a bit complex so it needs some explanation before we get into writing our code.

expression.TimeScaleData(StartDate, EndDate, Type, TimeScaleUnit, Count)

Where expression is one of the following:
An Assignment or Assignments Collection, A Resource or Resources Collection, A Task or Tasks Collection.

StartDate is the start date for the data. The twist here is that if the date you provide falls within an interval you are requesting, the StartDate is rounded to the start of that interval. For example if you are using TimeScaleUnits of months and enter a date in the middle of the month, StartDate will "round down" to the start of the first day of the month.

FinishDate is the finish date for the data. Like StartDate it rounds, but in this case to the end of the time interval you are using.

Type is the type of data. If you leave out this parameter the default is to return work. Even if you want work it is generally best to specify the type instead of leaving it blank. There is a long list of the defined types at the end of this article but to illustrate the type here are a few examples:

• pjAssignmentTimeScaledCumulativeCost
• pjTaskTimeScaledPercentComplete
• pjResourceTimeScaledWork

TimeScaleUnit is used to set the size of the time slice. By default weeks are used, but it is always good practice to set it explicitly. The possible values are:

• pjTimescaleYears
• pjTimescaleQuarters
• pjTimescaleMonths
• pjTimescaleWeeks
• pjTimescaleDays
• pjTimescaleHours
• pjTimescaleMinutes

Count is the final parameter. It controls how many timescale units are grouped together. Use it if you want to group the data by a timescale which is different than the ones available. For example if you wanted your data by half years, you could set TimeScaleUnit to pjTimescaleMonths and use a Count of 6.

The TimeScaledData method returns a TimeScaleValues collection. This is a collection which contains all of the timeslices and their values. We use the TimeScaleData method on an object and use the resulting TimeScaleValues as the source of our data.

Our Export form is built around this method. The key operation is: Set TSV = r.TimeScaleData(tbStart.Value, tbEnd.Value, TimescaleUnit:=cboxTSUnits.Value, 1) Now all we need to do is build a form which will supply the correct parameters and which will export the data to Excel.

Using a Form in VBA
Many of the examples on this site or my other site (link to masamiki.com) don't ask the user for much information, but the TimeScaleData method has a number of parameters that must be supplied. And some of those parameters are selected from a list. Because of this we need to move from the simple input box to a form. A form can have a number of different controls on it. This example is kept very simple. It has Start Date and End Date text boxes. Units and Hours or FTE combo boxes and a button which will run the code to export the data. A more fully developed version could have selections for Resource, Assignment or Task and could also have a list of all possible data types.

Here is the form I put together:

Behind the form there are a number of subroutines. The first one runs when the form is first shown.

Private Sub UserForm_Initialize()
'set the start date textbox value
tbStart = ActiveProject.ProjectStart
'set the end date textbox value
tbEnd = ActiveProject.ProjectFinish
'call a subroutine to set values for Units box
fillTSUnitsBox
'call a subroutine to set values for the hours or FTE box
fillFTEBox
End Sub

The routine to fill the Units box:

Sub fillTSUnitsBox()
'sets Units constants
Dim myArray(5, 2) As String
myArray(0, 0) = "Days"
myArray(0, 1) = pjTimescaleDays
myArray(1, 0) = "Weeks"
myArray(1, 1) = pjTimescaleWeeks
myArray(2, 0) = "Months"
myArray(2, 1) = pjTimescaleMonths
myArray(3, 0) = "Quarters"
myArray(3, 1) = pjTimescaleQuarters
myArray(4, 0) = "Years"
myArray(4, 1) = pjTimescaleYears

cboxTSUnits.List = myArray
'use weeks as default value
cboxTSUnits.Value = 3
End Sub

The routine to set the Hours/FTE box

Sub fillFTEBox()
'sets choice of FTE or Hours
cboxFTE.List = Array("Hours", "FTE")
'sets to hours
cboxFTE.Value = "Hours"
End Sub

The code which runs when the button is clicked

Private Sub btnExport_Click()
exportResourceUsage
End Sub

And finally at the heart of it all the exportResourceUsage subroutine:

Sub exportResourceUsage()
'first define our variables
Dim r As Resource
Dim rs As Resources
Dim TSV As TimeScaleValues
Dim pTSV As TimeScaleValues
Dim i As Long, j As Long
'define excel variables
Dim xlRange As Excel.Range
Dim xlCol As Excel.Range
Dim xlRow As Excel.Range
Dim xlApp As Excel.Application

'open excel and set the cursor at the upper left cell
Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlBook.Worksheets.Add
xlsheet.Name = ActiveProject.Name
Set xlRange = xlApp.ActiveSheet.Range("A1:A1")

'start writing column headers
xlRange.Value = "Resource Name"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "Generic"

'use the dates from the project summary task TSV to set column headings
Set pTSV = ActiveProject.ProjectSummaryTask.TimeScaleData(tbStart.Value, tbEnd.Value, TimescaleUnit:=cboxTSUnits.Value)
For j = 1 To pTSV.Count
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = pTSV.Item(j).StartDate
Next j

'go to first cell of next row
Set xlRange = xlRange.Offset(1, -j)

'loop through all resources and write out values
Set rs = ActiveProject.Resources
For Each r In rs
If Not r Is Nothing Then
xlRange.Value = r.Name
Set xlRange = xlRange.Offset(0, 1)
If r.EnterpriseGeneric Then
xlRange.Value = r.EnterpriseGeneric
End If

Set xlRange = xlRange.Offset(0, 1)

Set TSV = r.TimeScaleData(tbStart.Value, tbEnd.Value, TimescaleUnit:=cboxTSUnits.Value)
'loop through all timescale data and write to cells
For i = 1 To TSV.Count
If Not TSV(i).Value = "" Then
'convert to FTE if FTE is set
If cboxFTE.Value = "FTE" Then
Select Case cboxTSUnits.Value
Case 0 'years
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay * ActiveProject.DaysPerMonth * 12)

Case 1 'quarters
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay * ActiveProject.DaysPerMonth * 3)

Case 20 'months
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay * ActiveProject.DaysPerMonth)

Case 3 'weeks
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerWeek)

Case 4 'days
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay)

End Select
Else
'if not FTE, work hours are written
xlRange.Value = TSV(i).Value / (60)
End If
End If
Set xlRange = xlRange.Offset(0, 1)
Next i
End If
Set xlRange = xlRange.Offset(1, -(TSV.Count + 2))
Next r

'some minor excel formatting of results
xlApp.Rows("1:1").Select
xlApp.Selection.NumberFormat = "m/d/yy;@"
xlApp.Cells.Select
xlApp.Cells.EntireColumn.AutoFit
xlApp.Activate
End Sub

To keep this readable I've left out all error handling and the like. It is meant as an example of how to use the TimeScaleData method and read from the TimeScaleValues collection.

If you hadn't noticed, the code which runs when the button is clicked contains only one command. It is possible to put the whole exportResourceUsage subroutine code under the handler for that button, but by putting it in a separate subroutine, it is easier to reuse the subroutine and keep the code readable.

Now, we have the form and the code (rightclick here to save a copy of the form to your computer). The next step is getting the form to display. To do this we need to write a single line of code.

Sub showExportForm()
exportResourceTimescaledData.Show
End sub

This macro can then be assigned to a button on your toolbar. Clicking the button runs the macro which shows the form.

The TimeScaleData Type Reference
There are a large range of TimeScaleData types available and which can be used to produce everything from Earned Value S-charts to cashflows or resource availability. The types possible depend on whether you are looking at Assignment, Resource or Task data. For example, Percent Complete is only available when looking at a task or task collection. WorkAvailability is only relevant to resources.

The constants to specify the Type always follow the same format, beginning with "pj" then the type of object you are referencing (Task, Resource or Assignment), then "Timescaled" and finally the data you are looking for. For example you would use pjTaskTimescaledDataActualCost for actual cost for a task, but pjResourceTimescaledDataActualCost for costs of a specific resource.

The following are available for Tasks, Assignments, and Resources

• ActualCost
• ActualOvertimeWork
• ACWP (actual cost of work performed - use in Earned Value calculations)
• BaselineCost (and Baseline1-10 cost)
• BaselineWork (and Baseline1-10 work)
• BCWP (budgeted cost of work performed - used for Earned Value calculations)
• BCWS (budgeted cost of work scheduled - used for Earned Value calculations)
• Cost
• CumulativeCost
• CumulativeWork
• CV (cost variance - used in Earned Value)
• Overallocation
• OvertimeWork
• RegularWork
• SV (schedule variance - used in Earned Value)
• Work

The following are available for Assignments and Resources only:

• PeakUnits
• PercentAllocation

The following are available for Resources only:

• RemainingAllocation
• UnitAvailability
• WorkAvailability

The following are available for Tasks only:

• ActualFixedCost
• CPI (cost performance index)
• CumulativePercentComplete
• CVP
• FixedCost
• PercentComplete
• SPI (schedule performance index)
• SVP