PRJ: Macro to Share Resources in a Consolidated Project

MORE INFORMATION

Sharing resources in Microsoft Project 98 and later is different than in earlier versions of Microsoft Project. In earlier versions of Microsoft Project, you can create a resource pool as you consolidate projects. In Microsoft Project, you must create the relationships between the project files and the resource pool prior to creating the consolidated project.

If you have many projects, this process can become time consuming. To make this process faster, the following macro loops through all tasks within a consolidated project to determine what tasks are inserted projects. It opens the inserted project file and attaches it a new resource pool file called Pool.mpp, that it creates automatically.

NOTE: This macro is not necessary to share resources. It is intended to reduce the amount of time involved when creating resource sharing in a large number of projects.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
Note that a line that is preceded by an apostrophe (') introduces a comment in the code. Comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next.

   Sub SetPoolForConsolProjs()
   Dim oTasks As Tasks
   Dim oInsertedProj As Project
   Dim i As Long
   Dim sPoolName As String
   Dim bCalcMode As Boolean
   'Turn off calculation.
   bCalcMode = Application.Calculation
   Application.Calculation = pjManual
   On Error GoTo SetPoolForConsolProjs
   'Get tasks collection for consolidated project.
   Set oTasks = ActiveProject.Tasks
   cFile = ActiveProject.FullName

   '*************************
   'Create a new file and get name.
   'This is where you tell the macro which file is going to
   'be the resource pool. If you already have a resource
   'pool, you can do something like:
   'sPoolName = "c:\my documents\pool.mpp"
   'The following two message boxes prompt you to save the file used for
   'resource pool at the time you create it and then again when you are
   'finished with your project.

   MsgBox "Name your Resource Pool and click ""Save"" in the following " _
   & "box" & Chr(13) & " When you finished save changes to all files."
   Application.FileNew
   Application.FileSaveAs
   sPoolName = ActiveProject.FullName

   '**************************
   'Loop through all tasks and find inserted projects.
   For i = 1 To oTasks.Count

       If Not oTasks(i) Is Nothing Then
           'Test for inserted projects.
           If oTasks(i).SubProject <> "" Then
           'Get ready to operate on subprojects.
           'GetObject opens collapsed subprojects.
           'Note: If the inserted projects are expanded, then this whole
           'process runs much faster since the load and calculation
           'process doesn't have to occur.
               Set oInsertedProj = GetObject(oTasks(i).SubProject)

               'Activate the inserted project. If this fails, then it
               'means the inserted project isn't open in a window of
               'its own. Therefore, use the NewWindow method.
           On Error GoTo 0
           On Error GoTo InsertProjectNotOpen
           Projects("" & oInsertedProj & "").Activate
           On Error GoTo 0
           On Error GoTo SetPoolForConsolProjs

   'Set the pool connection to the designated pool.
   Application.ResourceSharing share:=True, Name:=sPoolName

   'Close window.
   Alerts False
   FileClose pjDoNotSave, False
   Alerts True
   Set oInsertedProj = Nothing

           End If
       End If

   Next i
   'Reactivate the master project.
   'Projects("" & oTasks.Parent & "").Activate
   ' Set the pool connection for the consolidated project to the
   ' designated pool
   Projects(cFile).Activate
   Application.ResourceSharing share:=True, Name:=sPoolName
   Application.Calculation = bCalcMode
   Exit Sub

   SetPoolForConsolProjs:
   Application.Calculation = bCalcMode
   Exit Sub

   InsertProjectNotOpen:
   WindowNewWindow "" & oInsertedProj & ""
   Resume

   End Sub