ConsolidateProjects Method: how can I import Sub Project Summary task Custom field contents?

Question

If possible I want to avoid using Resource Pools and embedded project files as I have had problems with these in a Sharepoint environment, so I am exploring using the ConsolidateProjects Method to create a schedule that consolidates all my sub projects.

Using this code

Application.ConsolidateProjects Filenames:=strFiles, AttachToSources:=False, NewWindow:=True

I can populate my project file with the data from all the sub project files that reside in the nominated folder. The only problem is that it does not import the contents of custom fields that relate to the sub project SUMMARY task. It DOES import the value from the standard Priority field, but does NOT import the value in the Custom field Text1.

It works fine (and very quickly) for sub project tasks (and their summary tasks) but not for the sub project Summary task.

How can I import the contents of custom fields that relate to the sub project Summary task? Will I need to open each file individually and extract it somehow? That will really slow the process down.

Thanks

Eric

Answer 1

The way a master with sub-projects works is that the Master file is considered a container. To this container you can insert sub-projects that are basically just pointing to file addresses. These open up either in read-only or read/write mode depending on how you set up your Master file.

The Master file then can only read / display columns that are common to both the Master file and the sub-projects, in particular the custom fields set up with formulas and such. So when I'm setting up a Master file, I make sure that all my sub-projects and Master file contain all the custom fields and formulas in the same columns. Example, Text1 has a formula that I will have in all of Text1 for all my files.

This way you will be able to access all custom fields from all your sub-projects in your master file.

Programmatically, you can access all the data from each of your sub-files and put them in an Excel sheet without worrying about what I talked about above. But that will not give you what you are asking for here.

Answer 2

This is probably not the most efficient solution, but it works.

The following code will consolidate my sub projects into a pre-formatted master file. It uses some array variables to capture the required data from each file in turn. All variables are publicly declared in a separate module.

Question: is it necessary to open the sub project files to do this, as this slows the process down somewhat?

Sub Consolidate()


Application.DisplayAlerts = False

DeleteTasks: ' Delete current contents of Master File For Each Tsk In ActiveProject.Tasks Tsk.Delete GoTo DeleteTasks ' Necessary as once the Task is deleted the variable Tsk is set to Nothing and the loop fails Next

' make sure you've added a reference (Tools>References)
' to "Microsoft Scripting Runtime"
Set fso = New FileSystemObject
Set SourceFolder = fso.GetFolder(SubProjFolderPath)

i = 1 ' count of files in source folder

'build a list of file names
For Each SourceFile In SourceFolder.Files
    If Right(SourceFile.Name, 3) = "mpp" Then
        strFiles = strFiles & SubProjFolderPath & SourceFile.Name & ListSeparator

        ' Open each file to capture key data in array variables
        FileOpen SourceFile
        ReDim Preserve arrPID(i)
        ReDim Preserve arrSubNames(i)

        arrPID(i) = ActiveProject.ProjectSummaryTask.Text30
        arrSubNames(i) = ActiveProject.ProjectSummaryTask.Name

        For Each Tsk In ActiveProject.Tasks
            Tsk.Text30 = ActiveProject.ProjectSummaryTask.Text30
        Next Tsk

        FileClose pjSave
        iMax = i ' equals the number of files in source folder to be added to master plan
        i = i + 1

    End If
Next SourceFile

If Len(strFiles) <> 0 Then
    'ConsolidateProjects with the file list
    strFiles = Left(strFiles, Len(strFiles) - 1)
    Application.ConsolidateProjects Filenames:=strFiles, AttachToSources:=False, NewWindow:=False
End If

Application.DisplayAlerts = True

Then I use this code to apply the values to each task in the newly populated Master plan file.

Sub RefreshCustomFields()

' Assign the Project ID from the Sub Project file to the relevant sub project summary task in the new MasterPlan
i = 1
For Each Tsk In ActiveProject.Tasks

    TaskName = Tsk.Name
    If i > iMax Then End ' to stop the loop after the final Source file name has been matched

    If TaskName = arrSubNames(i) Then
        Tsk.Text30 = arrPID(i) ' Text30 is set to be the PID

        i = i + 1
    End If
Next Tsk