Rule of Thumb

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 Strat, 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.