Sunday, February 17, 2013

Export Resource exceptions details (non Working Time) to MS Excel

Recently created a macro to export resource non working time from MS Project to 2007/2010/2013 to MS Excel. It iterate through all work resources in current project plan and check for exceptions. It then exports resource name, Exception start and finish time to excel.

Use below steps to implement it.

  1. Open the Project File
  2. Press Alt + F 11
  3. Right click on VBAProject(ProjectName) and select add Module
  4. Copy the below code in the module

======================
Sub ResourceExceptions()

Dim MyXL As Object
Set MyXL = CreateObject("Excel.Application")
MyXL.Workbooks.Add
MyXL.Visible = True

MyXL.ActiveWorkbook.worksheets.Add.Name = "Exception Report"
MyXL.ActiveWorkbook.worksheets("Exception Report").Activate

Set xlRange = MyXL.activesheet.Range("A1")

xlRange.Range("A1") = "Resource Name"
xlRange.Range("B1") = "Start Time"
xlRange.Range("C1") = "Finish Time"
i = 2
    For Each R In ActiveProject.Resources
        If Not (R Is Nothing) Then
        ctr = 1
        If R.Type = pjResourceTypeWork Then
        If R.Calendar.Exceptions.Count > 0 Then
        For Each E In R.Calendar.Exceptions
        xlRange.Range("A" & i) = R.Name
        xlRange.Range("b" & i) = R.Calendar.Exceptions.Item(ctr).Start
        xlRange.Range("c" & i) = R.Calendar.Exceptions.Item(ctr).Finish
        ctr = ctr + 1
        i = i + 1
        Next
        End If
        End If
        End If
    Next R
End Sub
================
  1. You can run this macro using view >> Macro >> view macro >> select ResourceExceptions and click run
  2. To make this macro available for all project copy this macro in global.mpt. File >> Info >> Organizer >> Module >> Select "ResourceExceptions" from Project to global.mpt


1 comment:

  1. Hi Kiran
    This is very Helpful Macro....Thanks for it.
    I also want to add customer calender in the which we create for the part-time resources. How to refer exception of those customer calenders?

    ReplyDelete