Macro to Close Out Project

[Sean]

I've tested this code informally and it has worked successfully with
repeated testing. Feel free to use it if you like. My goal is to have
it robust enough to distribute to end users and have them use this to
close out projects rather than going through multiple steps that only
introduce chances for error. Any input or feedback you have would be
great.

----------------------------------------
Sub Close_Out_Project()
' Macro Close Out Project
' Last Updated Aug 21 2007 by Sean Sullivan
' Closes out a project by setting remaining work to 0, setting
milestones to 100% complete,
' and changing resource booking types to Proposed.
Dim Tsk As Task
Dim Rsrc As Resource
Dim RowCount As Integer

'Since it uses the Resource Sheet, it will only work for resources
in the active project, not all projects.
'Must check for only one project to be open.
If Application.Projects.Count <> 1 Then
MsgBox ("This macro can only be run if one (1) project is
open. Please close all projects except the one you would like to close
out.")
Exit Sub
Else
End If

Application.ViewApply ("Enterprise Gantt Chart")

'Set remaining work = 0 for non-summary tasks
For Each Tsk In ActiveProject.Tasks
If Not Tsk Is Nothing Then
If Tsk.OutlineChildren.Count = 0 Then 'weeds out summary
tasks
Tsk.RemainingWork = 0
'For a task with no work done, this results in a 0 work
task and becomes a milestone.
'Set milestone %complete to 100
If Tsk.Work = 0 Then
Tsk.PercentComplete = 100
End If
End If
End If
Next Tsk

'Recalculate fields so everything is accurate
CalculateAll

If ActiveProject.Tasks(1).PercentComplete = 100 Then
MsgBox ("Project 100% Complete")
Else
MsgBox ("Please check to make sure your project is 100%
Complete.")
Exit Sub
End If

'Open Resource Sheet to edit Booking Types
Application.ViewApply ("Resource Sheet")
Application.TableApply ("Project Guide: Booking Resource Sheet")

'Set Counter Variable
RowCount = 1

'Set Resource Booking Type to Proposed
For Each Rsrc In ActiveProject.Resources
If Not (Rsrc Is Nothing) Then 'test for blank resource
SelectResourceField Row:=RowCount, Column:="Booking Type",
RowRelative:=False
SetResourceField Field:="Booking Type", Value:="Proposed"
End If
RowCount = RowCount + 1
Next Rsrc

If ActiveProject.Resources.Count = RowCount - 1 Then
MsgBox ("All Resources Changed")
Else 'if not all the resources changed, then manually adjust
through this dialog
MsgBox ("Please change resource bookings to 'Proposed'")
EnterpriseTeamBuilder
End If

Application.TableApply ("Entry")
Application.ViewApply ("Enterprise Gantt Chart")

End Sub

 

[Jack Dahlgren]

Sean,

Haven't had a chance to look all the way through it, but it looks like a
reasonable process. There are a few things you could clean up.

One suggestion is to use
if not tsk.summary

instead of

If Tsk.OutlineChildren.Count = 0 Then 'weeds out summary tasks

It is easier for most people to understand.

Also, it is not clear to me why you are selecting resource lines in the
resource sheet. Can't you just work through all the resources without
displaying that view?

-Jack

 

[Sean]

Thanks for the feedback. I'll definitely integrate the tsk.summary
code.

I wasn't able to find a way to change the resource booking types
without using a resource sheet and changing columns that way. See
thread here:
http://groups.google.com/group/micr...eloper/browse_thread/thread/11afe00a8c2dc5de#

I had no luck using the Resource.bookingtype property, so this is what
I opted for. I do my best to keep it hidden from the user, but if
there's a better way to hide that I'm all ears!

Again, thanks for the feedback.

--Sean

 

[Jack Dahlgren]

Sean,

Try this to set the resource booking type:

Sub Jack_Dahlgren_resource_reset()
For Each Resource In ActiveProject.Resources
Call Resource.SetField(FieldNameToFieldConstant("Booking Type", pjResource),
"Proposed")
Next Resource
End Sub

Works for me in Project Pro 2007
FieldNameToFieldConstant is essential for working with enterprise fields.

-Jack Dahlgren

 

[Jonathan Sofer]

Nice one Jack! Very useful. I have seen that trick before but can't recall
where

Jonathan Sofer

 

[Sean]

Hi Jack,

Thanks for the code, but I'm still getting a 1004 error on the line
with the Call method. I tried both your code snippet and one nearly
the same, but neither has results. My other attempt replaced the
FieldNameToFieldConstant function with the value it returned,
pjResourceBookingType, as found here (value 205521595):

https://msdn2.microsoft.com/en-us/library/bb223192.aspx

I'm using Project Pro 2003, so maybe that makes a difference? I'm not
sure how. Would there be any server settings that affect this? I'm
still grasping at straws for reasons why this function fails. It's
clear that it works for others, but for some reason not for me.

--Sean

 

[Jack Dahlgren]

Sorry,

My sample is for Project Professional 2007. I don't have 2003 available so I
can't help debug.
I think there are probably differences between the two as the database and
file format are slightly different.

-Jack