Export Nonworking Time

Question

I'm using MS Project 2010 and a shared resource pool with approximately 80 resources. To account for vacation time, each person has changed their working time to reflect this information. Is there a way to standard way to export this data (e.g. into MS Excel)? Through research I've come across a few suggestions using macros but I'm hoping MS Project has something already built-in.

Answer 1

Kelly,

You might find this useful.

Option Explicit

Sub CalendarExceptions()

'Basic macro code created by Kiran.K and posted on MSDN Project

' customizing and programming forum Feb 7,2013

'Code streamlined and updated by John - Project June 2,2014

Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")

Dim i As Integer, j As Integer

Dim E As Exception

Dim r As Resource

Dim xlRng As Range

'open Excel, define workbook, and set column headers

MyXL.Workbooks.Add

MyXL.Visible = True

MyXL.ActiveWorkbook.Worksheets.Add.Name = "Exception Report"

MyXL.ActiveWorkbook.Worksheets("Exception Report").Activate

Set xlRng = MyXL.ActiveSheet.Range("A1")

xlRng.Range("A1") = "Proj Cal Holidays"

xlRng.Range("B1") = "Start Date"

xlRng.Range("C1") = "Finish Date"

xlRng.Range("E1") = "Res Name"

xlRng.Range("F1") = "Res Base Cal"

xlRng.Range("G1") = "Base Cal Excep"

xlRng.Range("H1") = "Start Date"

xlRng.Range("I1") = "Finish Date"

xlRng.Range("K1") = "Resource Name"

xlRng.Range("L1") = "Res Excep"

xlRng.Range("M1") = "Start Date"

xlRng.Range("N1") = "Finish Date"

'First gather and export Project calendar exceptions

i = 2

If ActiveProject.Calendar.Exceptions.Count > 0 Then

    For Each E In ActiveProject.Calendar.Exceptions

        xlRng.Range("A" & i) = E.Name

        xlRng.Range("B" & i) = E.Start

        xlRng.Range("C" & i) = E.Finish

        i = i + 1

    Next

End If

'Next, gather and export resource base calendar exceptions along with

'   resource calendar exceptions

i = 2

For Each r In ActiveProject.Resources

    If Not r Is Nothing Then

        j = i

        If r.Type = pjResourceTypeWork Then

                For Each E In r.Calendar.BaseCalendar.Exceptions

                    xlRng.Range("E" & i) = r.Name

                    xlRng.Range("F" & i) = r.Calendar.BaseCalendar.Name

                    xlRng.Range("G" & i) = E.Name

                    xlRng.Range("H" & i) = E.Start

                    xlRng.Range("I" & i) = E.Finish

                    i = i + 1

                Next E

                For Each E In r.Calendar.Exceptions

                    xlRng.Range("K" & j) = r.Name

                    xlRng.Range("L" & j) = E.Name

                    xlRng.Range("M" & j) = E.Start

                    xlRng.Range("N" & j) = E.Finish

                    j = j + 1

                Next E

        End If

    End If

Next r

MyXL.ActiveWorkbook.Worksheets("Exception Report").Columns("A:N").AutoFit

End Sub

John

Answer 2

Hi,

I'm afraid your hope is in vain. You will need programming (presumably VBA).

Greetings,