none
MS Project Resource Holiday Calendar RRS feed

  • Question

  • Hi All, 

    Long story short, we would like our resource calendars to be updated by a single person (rather than the user). Our HR system allows us to pull a report as CSV which includes usernames and dates of when they are on holiday. Those match our enterprise resource pool. My question is twofold. 

    1. Is there a way to import this info into the resource's calendar?

    2. If No is the answer for 1. can we just create a bucket project plan for all holiday entries? Is there a neat solution that allows us to use this in the resource management system?

    A response would be hugely appreciated. 

    Thanks 

    Kev

    Wednesday, July 3, 2019 11:31 AM

All replies

  • Hi Kev,

    does the following code help?

    Sub ImportAbsence()
    Const c_Path = "C:\Users\Barbara Henhapl\Desktop\Absence.csv"
    Const c_AbsenceDescription = "Absence"
    Const c_SplitDelimiter = ";" 'In German regional settings, csv delimiter is ';' Adjust accordingly
    
    Dim FileNum As Integer
    Dim DataLine As String
    Dim R As Resource
    Dim P As Project
    Dim Cal As Calendar
    Dim Excep As Exception
    Dim v_AbsenceDate As Date
    
    
    Set P = ActiveProject
    If Not P Is Nothing Then
        If P.Type = pjProjectTypeEnterpriseResourcesCheckedOut Then
            FileNum = FreeFile()
            Open c_Path For Input As #FileNum
            While Not EOF(FileNum)
                Line Input #FileNum, DataLine ' read in data 1 line at a time
                Set R = ActiveProject.Resources(Split(DataLine, c_SplitDelimiter)(0))
                v_AbsenceDate = Split(DataLine, c_SplitDelimiter)(1)
                'Delete all exceptions in future if resource is checked-out
                If Not R Is Nothing And v_AbsenceDate >= Date Then
                    Set Cal = R.Calendar
                    For Each Excep In Cal.Exceptions
                        If Excep.Start >= Date Then
                            Excep.Delete
                        End If
                    Next Excep
                    'add only exceptions in future
                    Cal.Exceptions.Add Type:=0, Start:=v_AbsenceDate, Finish:=v_AbsenceDate, Name:=c_AbsenceDescription
                End If
            Wend
        Else
            MsgBox "No 'Checked-out Enterprise Resources' - nothing to do"
        End If
    End If
    End Sub
    

    Regards
    Barbara


    Barbara Henhapl Blog (English/German) MVP

    Wednesday, July 3, 2019 8:31 PM
    Moderator