Export Resource Usage with time-phased data MS Project Standard 2013

Question

I need to export Resource Usage monthly data to Excel for data analysis.

Answer 1

Marie --

The best way to handle this would be to create a custom Visual Report for Excel.  Your Visual Report would need to be created from Assignment Usage data.  In the PivotTable, you would need to include the following:

• Row Fields drop area - Resources, Tasks
• Column Fields drop area - Time Weekly Calendar
• Values drop area - Work

I think that is the closest you can get to the Resource Usage view in Excel, short of using VBA to push the data to Excel.  Just a thought.  Hope this helps.

Answer 2

I have same problem with Marie, and i found this 

http://zo-d.com/blog/archives/programming/analyze-microsoft-project-resource-usage-data-in-excel.html

Base from that, i have modified the program to suit my needs. 

It looks like working well to export resource usages to excel.

However, when i want to run it, sometimes it has errors that i cant understand why it's happened.

I am not good in vba so someone pls help me to fix it ?

Here is my code : (The error occurred at places in bold)

Private Sub btnExport_Click()

exportResourceUsage

End Sub

Sub exportResourceUsage()

Dim r As Resource

Dim rs As Resources

Dim Assign As Assignment

Dim Assigns As Assignments

Dim TSV As TimeScaleValues

Dim pTSV As TimeScaleValues

Dim I As Long, J As Long

Dim xlRange As Excel.Range

Dim xlEnd As Excel.Range

Dim xlCol As Excel.Range

Dim xlRow As Excel.Range

Dim xlApp As Excel.Application

Dim End_row As Integer

Dim T As Integer

Dim C As Integer

Dim N1 As String

Dim N2 As String

'open excel and set the curson at the upper left cell

Set xlApp = New Excel.Application

xlApp.Visible = True

AppActivate "Excel"

Set xlBook = xlApp.Workbooks.Add

Set rs = ActiveProject.Resources

For Each r In rs

    Set xlsheet = xlBook.Worksheets.Add

    xlsheet.Name = r.Name

    Set xlRange = xlApp.ActiveSheet.Range("A1:A1")

    'start writing column headers

    xlRange.Value = "Project"

    Set xlRange = xlRange.Offset(0, 1)

    xlRange.Value = "Work"

    '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 + 2)

    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

            xlRange.Value = TSV(I).Value / (60)

        End If

        Set xlRange = xlRange.Offset(0, 1)

    Next I

    Set xlRange = xlRange.Offset(1, -TSV.count - 2)

    For J = 1 To r.Assignments.count

        Set Assign = r.Assignments(J)

        xlRange.Value = Assign.Project

        Set xlRange = xlRange.Offset(0, 2)

        Set TSV = Assign.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

                xlRange.Value = TSV(I).Value / (60)

            End If

            Set xlRange = xlRange.Offset(0, 1)

        Next I

        Set xlRange = xlRange.Offset(0, -(TSV.count + 1))

        Cells(J + 2, 2) = xlApp.WorksheetFunction.Sum(Range(Cells(J + 2, 3), Cells(J + 2, TSV.count + 2)))

        Set xlRange = xlRange.Offset(1, -1)

    Next J

    'Sort A to Z

    Range("A3", Cells(r.Assignments.count + 2, TSV.count + 2)).Select

    xlsheet.Sort.SortFields.Clear

    xlsheet.Sort.SortFields.Add Key:=Range("A3"), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With xlsheet.Sort

        .SetRange Range("A3", Cells(r.Assignments.count + 2, TSV.count + 2))

        .Header = xlNo

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

'some minor excel formatting of results

xlApp.Rows("1:1").Select

xlApp.Selection.NumberFormat = "m/d/yy;@"

xlApp.Cells.Select

xlApp.Cells.EntireColumn.AutoFit

Next r

exportResourceTimescaledData0.Hide

End Sub

Private Sub UserForm_Initialize()

tbStart = ActiveProject.ProjectStart

tbEnd = ActiveProject.ProjectFinish

fillTSUnitsBox

End Sub

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 = 4

End Sub

Private Sub UserformHide_Click()

exportResourceTimescaledData0.Hide

End Sub

Answer 3

Marie --

You are more than welcome for the help, my friend.  :)

Answer 4

Dale, this was very helpful.  I also found a less robust solution by copying from Resource Usage and pasting to Excel taking care to line up the resources and the time/work. 

I found  your answer to my next question "how to save the template" under "I cannot save a visual reports template" It was for 2010 but I was able to work my way through it in 2013.

Thank you,

Marie