Extracting Microsoft Project Timephased Data

It is easy to get complete tasks, resources or assignments work, units or cost, but getting this data split by weeks or months is a real pain. Microsoft Project does provide a table namend MSP_TIMEPHASED_DATA, but due to Microsoft Project’s performance reasons it is not stored in way that it would be easily extracted.

Microsoft provides some SQL scripts that can help you out if you’re using Microsoft SQL Server, but working with Microsoft Query on Microsoft Access database this doesn’t help much.

But there’s another way to tacle this. We can use Microsoft Visual Basic and a bit of programming to get the data into Microsoft Excel.

  1. From Microsoft Project select Tools, Macro then Microsoft Visual Basic Editor.
  2. Select Tools and References and make sure that Microsoft Project 11.0 Object Library and Microsoft Excel 11.0 Object Library are selected.

  1. In Visual Basic select Insert, then Module.
  2. You will see a new module in the Project Explorer pane on the tope left part of the window.
  3. In the Properties Window pane, click on the name of the newly created module ("Module#") and rename it into ExportTimephasedResourceData.
  4. Copy the following code into the editor:

Sub ExportTimephasedResourceData()

' Sub will export timephased resource data (work, cost) into
' Microsoft Excel worksheet. The output is data in Excel
' spreadsheet, which you can then use to create a pivot table.

' Define time interval for timephased data
Dim Start, Finish As String
Start = "1.1.2004"
Finish = "31.5.2004"

' Define timescale unit. Can be one of the following
' PjTimescaleUnit constants:
' pjTimescaleYears, pjTimescaleQuarters, pjTimescaleMonths,
' pjTimescaleWeeks, pjTimescaleDays, pjTimescaleHours,
' pjTimescaleMinutes
Dim TimescaleUnit As PjTimescaleUnit
TimescaleUnit = pjTimescaleMonths

Dim Pj As Project
Dim PjRes As Resources
Dim XlApp As Excel.Application
Dim IdSheet As Integer
Dim XlSheet As Excel.Worksheet
Set Pj = ActiveProject
Set PjRes = Pj.Resources
Set XlApp = New Excel.Application
XlApp.Visible = False
Set XlBook = XlApp.Workbooks.Add
XlBook.Title = Pj.Title
Set XlSheet = XlBook.ActiveSheet
Dim TSVWork As TimeScaleValues
Dim TSVCost As TimeScaleValues
Dim T As Long
Dim R As Long

Dim Row As Integer

' Choose work unit divisor depending on the Tools | Options | Schedule | Work.

' Work is stored in minutes in MS Project.

Select Case Pj.DefaultWorkUnits

Case pjMinute

d = 1

Case pjHour

d = 60

Case pjDay

d = Pj.HoursPerDay * 60

Case pjWeek

d = Pj.HoursPerWeek * 60

Case pjMonthUnit

d = Pj.DaysPerMonth * Pj.HoursPerDay * 60

Case Else

d = 1

End Select

' Set up currency format for Excel

CurrencyFormat = SetCurrencyFormat(Pj)

If Pj.Resources.Count > 0 Then

XlSheet.Cells(1, 1) = "Group"

XlSheet.Cells(1, 2) = "Resource"

XlSheet.Cells(1, 3) = "Date"

XlSheet.Cells(1, 4) = "Work"

XlSheet.Cells(1, 5) = "Cost"

Row = 2

For R = 1 To Pj.Resources.Count

Set TSVWork = PjRes(R).TimeScaleData(Start, Finish, _
Type:=pjResourceTimescaledWork, TimescaleUnit:=TimescaleUnit)

Set TSVCost = PjRes(R).TimeScaleData(Start, Finish, _
Type:=pjResourceTimescaledCost, TimescaleUnit:=TimescaleUnit)

For T = 1 To TSVWork.Count

If Not TSVWork(T).Value = "" And Not TSVCost(T).Value = "" Then

XlSheet.Cells(Row, 1) = PjRes(R).Group

XlSheet.Cells(Row, 2) = PjRes(R).Name

XlSheet.Cells(Row, 3) = TSVWork(T).StartDate

Select Case TimeUnits

Case pjTimescaleMonths

XlSheet.Cells(Row, 3).NumberFormat = "Mmm Yy"

End Select

If Not TSVWork(T).Value = "" Then

XlSheet.Cells(Row, 4) = TSVWork(T).Value / d

XlSheet.Cells(Row, 4).NumberFormat = "#,##0"

End If

If Not TSVCost(T).Value = "" Then

XlSheet.Cells(Row, 5) = TSVCost(T).Value

XlSheet.Cells(Row, 5).NumberFormat = CurrencyFormat

End If

Row = Row + 1

End If

Next T

Next R

End If

XlApp.ScreenUpdating = True

MSProject.ScreenUpdating = True

'and finally display a message that we are finished

AppActivate "Microsoft Project"

XlApp.Visible = True

AppActivate "Microsoft Excel"

End Sub

Function SetCurrencyFormat(Pj As Project)

' Set currency number format

CurrencyFormat = ""

Select Case Pj.CurrencySymbolPosition

Case pjBefore

CurrencyFormat = """" & Pj.CurrencySymbol & """"

Case pjBeforeWithSpace

CurrencyFormat = """" & Pj.CurrencySymbol & """" & " "

End Select

CurrencyFormat = CurrencyFormat & "#,##0"

If ActiveProject.CurrencyDigits > 0 Then

CurrencyFormat = CurrencyFormat & "."

For i = 1 To Pj.CurrencyDigits

CurrencyFormat = CurrencyFormat & "0"

Next i

End If

Select Case Pj.CurrencySymbolPosition

Case pjAfter

CurrencyFormat = CurrencyFormat & """" & Pj.CurrencySymbol & """"

Case pjAfterWithSpace

CurrencyFormat = CurrencyFormat & " " & """" & Pj.CurrencySymbol & """"

End Select

SetCurrencyFormat = CurrencyFormat

End Function

The output is exported into new Microsoft Excel spreadsheet, which is easily converted into pivot table.




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

18 Responses to “Extracting Microsoft Project Timephased Data”

  1. Michael Singleton Says:

    How do I call this? Using it as a macro simply throws an error.

  2. Sašo Says:

    Let me know, where exactly does macro crash. It will highlight the line with yellow.

    What does the error message say?

  3. Michael Hoare Says:

    Hi – this looks like a very useful Module – but I can’t seem to debug the following error. “Run-time error 1004, Unable to set teh outline level property of the Range Class”. This occurs on the line:

    XlSheet.Rows(T + 1).OutlineLevel = PjTask.OutlineLevel

  4. Michael Hoare Says:

    Also – do I need to be on a particular view to execute the macro, or can I be on the Gantt, Usage or Resource Sheet Views?

  5. Sašo Says:

    Michale, try commenting the line (put ‘ in front of the line) for a start.

    Maybe you could also set a breakpoint on that line. Click on it and press F9. Then run the script and when it stops on the breakpoint, point the mouse to PjTask.Outline and check whether the number, which will be displayed makes sense or not.

    Regarding the view, there is no need to be on a particular view.

  6. Thomas Vanparys Says:

    Hi there,

    Great article. I’ve stumbled across your site while researching Monte Carlo simulations and ended up reading this because I’ve run into the same resource export issue myself a while ago.

    I have developed a similar module which provides a little more flexibility for the end user but is focused on the work rather than the cost.

    I was wondering whether you would mind if I included some of your ideas in my module?

    With regards to the flexibility I mentioned the module I’m maintaining allows you to do the following at execution:
    - pick your date range for the export
    - choose from several data types such as Availability to work, Work Assigned, Remaining Availability, Percent Allocation or Overallocation.
    - choose the size of the time units to export (months, weeks, days etc.)
    - pick whether you want to export hours or FTE equivalents data
    - include any of up to 70 commonly used fields from the resource table in the export.

    It’s freely downloadable from http://www.badsoda.com/tools/time-scaled-resource-data-exporter/

  7. Allan Says:

    very very useful.. but you have to search and replace the “” and ‘ characters in a text editor before copying it into your project.

    I also went to the assignment level by adding another for loop within the Resources loop and looping through all the resource(R).assignments(a).

    good good good thanks!

  8. Ashley Says:

    Fantastic Stuff.

    As previously mentioned: Find and replace “ , ” and ‘

    there is a typo as well change “strat” to “start”

    Really useful, Thank You!

  9. Bruce Weber Says:

    I am having trouble running the MCS macro.
    Error in the SetUpExcel subroutine.
    “user-defined type not defined”.

    MS Project 2007 and MS Excel 2007.

    thanks,
    Bruce

  10. Sašo Says:

    Bruce, Launch Visual Basic within MS Project (Alt-F11 or View | Macros | Visual Basic), select Tools | References and tick Microsoft Excel v?? Object Library. Let me know if it works.

  11. Sašo Says:

    Launch Visual Basic within MS Project (Alt-F11 or View | Macros | Visual Basic), select Tools | References and tick Microsoft Excel v?? Object Library. Let me know if it works.

  12. Patryk Says:

    I get an error:
    Run-time error ’1011′. The argument value is not valid

    at the line:
    Set TSVWork = PjRes(R).TimeScaleData(Start, Finish, _
    Type:=pjResourceTimescaledWork, TimescaleUnit:=TimescaleUnit)

    Any idea why is that?

  13. Sašo Says:

    I made a slight change, which may resolve your issue. Please let me know if it works.

  14. Patryk Says:

    Now I get “Type mismatch” error

    at line:

    If Not R Is Nothing Then

  15. Sašo Says:

    I changed the script back to the original. Your problem is comming out of problem with copy & pasting this code. All lines ending with underscore (_) should not be followed by an empty line.

    I’ll try to format the code in a way to overcome this, but in the meantime, just do the above.

  16. Patryk Says:

    I Think it’s not the case. I tried some other VBA codes with TimeScaleData and in all of them the problem repeats (argument value is not valid).

    It may be a problem with entering start and finish dates, all other values seem to be correct, but when I set Start and Finish only, it shows this error (1101). I don’t know if it is the problem of Project 2010 or any other.

  17. Sašo Says:

    It can be date formatting issue. To avoid it, replace Start, Finish with the following lines:

    Start = Pj.ProjectStart
    Finish = Pj.ProjectFinish

    And move them after Set Pj = ActiveProject

  18. Patryk Says:

    OK, now it works, thanks for the advice :)

Leave a Reply