« Bay Bridge at Sunrise | Main | Office VBA for Mac After 2008 »

Analyze Microsoft Project Resource Usage Data In Excel

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
'call a subroutine to set values for the hours or FTE box
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()
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
'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.Selection.NumberFormat = "m/d/yy;@"
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()
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

The Form which contains this code can be found here: ExportTimescaledData.zip. Unzip and then open project, hit ALT+F11 to open the visual basic editor. From the file menu in the editor select "Import File" and browse to the file. Don't forget to write a macro to make the form show like I explained earlier. It is useful to have that macro assigned to a button on the toolbar.
  • VBA Writing to a text file (MS Project, Excel)
  • MS Project VBA - Trim Function
  • Microsoft Project Undo Levels and Macros
  • Office VBA for Mac After 2008
  • Working with the Project Object
  • Working with the Tasks Collection
  • Setting Microsoft Project Level Custom fields using VBA
  • VBA returns in Excel 2011 for Mac
  • Iterating through Microsoft Project Subprojects
  • VBA and Visual Basic For ... to ... statements

  • Comments (12)

    Stephen Fadullon:

    Do you have a VBA program that can transfer the timescale data in months to an accounting month? Then, can it also import these data back to MS Project?

    See response in microsoft.public.project.developer newsgroup. -Jack



    this looks like a useful set of code as I'm currently trying to provide some decent reports for my boss. I haven't got a lot of experience with VBA though.

    I can't find the form you refer to (rightclick here to download form). Can you point me in the right direction please?




    Like Paul, I'm also looking for the sample form in this article.

    I'm having a problem with my form and would like to compare it with your original.

    Really looking forward to using this code!

    I just added the form as a download. Give it a try. -Jack

    Peter Aukes:


    The code for exporting data tot excel is great!!

    I have just one problem i got a failure executing the macro.
    When the line: Dim xlRange As Excel.Range is executed i got ta compile error: user-defined type not defined.

    Could you help me to solve this problem??




    Excellent. This code is more than usefull, it saves my job! :-)
    By the way, Since I have my timephased datas on excel ( as a generic profil ) , how can I manage to export those timephased data form XL to a specific task in MS project?
    Thank you for your help. Claude


    I tried your example, but keep getting a User Defined Type not defined error. This is on the line:

    xlRange As Excel.Range

    I am using Excel 2007 and MS Project 2003.

    Are you able to help me.

    Make a reference to excel by going to the tools menu, select References, then browse to find the Microsoft Excel 12.0 Object library. CHeck the box next to it. -JD


    Great website. Many thanks. I have a few problems.

    1.I couldnt import the frx file. Does this contain the code? In the end I copied and paste from webpage.

    If you import the form, the frx will come with it. You do not need to do anything about it as it should be taken care of in the import automatically -JD

    2. In the main file, exportResourceUsage, you have left the type work data as blank? You suggest you shouldnt leave it blank!
    Do as I say, not as I do. -JD

    3. I am just using the exportResourceUsage sub routine. I have defined the deafult parameters. I have run the sub-routine and fell over at:

    Dim xlRange As Excel.Range

    with compile error of "user-defined type not defined".
    Open the visual basic editor and make sure that you have selected the form or code you are working on. From the tools menu, select references and browse to the Microsoft Excel object library. Make sure there is a check next to it. - JD

    Many thanks,



    Does this sample work with Microsoft Excel 11.0 Object library?

    In my preview comment, I mensioned that I was getting Run-time error '1101' and this might be because of this?


    It MAY work with Excel 2003, but I have not tested with it. You should make sure that the references are correct. The easiest way to find the issue is to use the debugger and see where it is breaking. Set a break point just above that and then run it again. When you hit the break point, step through the code line by line and see what is going on. -Jack


    I'm really thank you for your excellent work!
    It is exactly what I look for.

    It works well.

    But one more thing remains.

    How can I also export 'actual work(or actual effort)'?


    How can I use custom fields in macros, in MSP 2007?

    Use GetField(customfieldnametofieldid(your field name, fieldtype)) - I should put up a post about this. -Jack

    Andy Kennedy:

    thanks! 10 minutes and i have it working for assignments - been looking for a way to do this for weeks

    William Vizcaino:


    This is a nice tool. I am running the Macro, but I get a run-time error '1101'
    "The argument value is not valid"

    in the line:
    Set pTSV = ActiveProject.ProjectSummaryTask.TimeScaleData(tbStart.Value, tbEnd.Value, TimescaleUnit:=cboxTSUnits.Value)
    Can someone Help me??


    Check the settings for timescale in the main form.

    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)


    The previous article is Bay Bridge at Sunrise.

    The next article is Office VBA for Mac After 2008.

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34