Quick Links

Reply To: Displaying Dates and Amounts of Resource Allocation

Home Forums Discussion Displaying Dates and Amounts of Resource Allocation Reply To: Displaying Dates and Amounts of Resource Allocation


Hello Michael,

I believe it is not possible to create such a custom view. I have once used the following VBA routine to list overallocated hours in an Excel sheet.

Sub ListOverAllocated()

Dim tsvs As TimeScaleValues

Dim i As Integer
Dim ResCount As Integer
Dim Rows As Integer

Dim XLApp As New Excel.Application
Dim Wbook As Workbook

Set Wbook = XLApp.Workbooks.Open(ActiveProject.Path + “OverAll.xls”)

Wbook.Sheets(“Sheet1”).Cells(1, 1).Value = “Overallocated Resource Name”

Wbook.Sheets(“Sheet1”).Cells(1, 2).Value = “Date Overallocated”

Wbook.Sheets(“Sheet1”).Cells(1, 3).Value = “Hours Overallocated”

Rows = 2
For ResCount = 1 To ActiveProject.NumberOfResources

If ActiveProject.Resources(ResCount).Overallocated Then

Wbook.Sheets(“Sheet1”).Cells(Rows, 1).Value = ActiveProject.Resources(ResCount).Name
Rows = Rows + 1

Set tsvs = _
ActiveProject.Resources(ResCount).TimeScaleData( _
ActiveProject.ProjectStart, _
ActiveProject.ProjectFinish, _
pjResourceTimescaledOverallocation, _
pjTimescaleDays, 1)

For i = 1 To tsvs.Count
If Not tsvs(i).Value = “” Then
Wbook.Sheets(“Sheet1”).Cells(Rows, 2).Value = CDate(tsvs(i).StartDate)
Wbook.Sheets(“Sheet1”).Cells(Rows, 3).Value = tsvs(i).Value / 60
Wbook.Sheets(“Sheet1”).Cells(Rows, 4).Value = “hrs”
Rows = Rows + 1
End If
End If
Wbook.Close savechanges = yes
End Sub

You need to create an empty Excel sheet named “OverAll.xls” in the same directory as the mpp file before running the macro. Click Alt + F11 to open VBE and copy the code above into a module and make sure “Microsoft Excel 1x.0 Object Library” checkbox is turned on  ( [x] ) in References dialog box (Tools | References…). I did not have a chance to improve it. Pls consider testing it in a backup of your project plan and post for feedback. I hope it works.