Microsoft project Professional uses calendar extensively for scheduling tasks and leveling resources, but the API for calendars are not exposed in project server to list out all the non-working day, so here is a small piece of macro which uses calendar object loops through all the calendars and pushes the data to a custom table , and now you can report onto it as you wish,
Thanks to colleague Parth for sharing this code 🙂
==================================================================================
Attribute VB_Name = “Module1”
‘Created By : Parth Rawal
‘Purpose : To Create the Exception data repository in the custom database for each project
Dim conDataAss As New ADODB.Connection
Dim cmd As New ADODB.Command
Sub calendar()
On Error GoTo Errorhandler
Dim cal As calendar
Dim i, j As Integer
Dim calName As String
j = 1
calName = ActiveProject.calendar.Name
i = ActiveProject.BaseCalendars(calName).Exceptions.Count
Dim rs As New ADODB.Recordset
With conDataAss
conDataAss.Open “Provider=sqloledb;” & _
“Server=ServerName;” & _
“Database=CustomDB;” & _
“User Id=userID;” & _
“Password=password”
End With
cmd.ActiveConnection = conDataAss
cmd.CommandText = “CustomDB_PROJECT_CALENDAR” ‘SP Name
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
For Each cal In ActiveProject.BaseCalendars
If cal.Name = calName Then
Do While j <= i
‘to insert the records for each project in the Custom database
If (conDataAss.State = ObjectStateEnum.adStateClosed) Then
conDataAss.Open ‘If connection is closed then open the connection
End If
cmd.Parameters(1).Value = CStr(ActiveProject.ProjectSummaryTask.Guid)
cmd.Parameters(2).Value = CStr(cal.Guid)
cmd.Parameters(3).Value = cal.Name
cmd.Parameters(4).Value = cal.Exceptions.Item(j).Name
cmd.Parameters(5).Value = cal.Exceptions.Item(j).Start
Set rs = cmd.Execute()
j = j + 1
Loop
End If
Next cal
conDataAss.Close
Exit Sub
Errorhandler:
MsgBox Err.Description
End Sub
==================================================================================
Leave a Reply