VBA to use all projects in folder as subprojects

Hi there.

I work for a commercial construction company. We have up to 2 dozen projects going at any given time. Our team has a folder we share called (surprise) "Schedules". There is a sub folder called "Open Projects". When we receive a contract for a new project, one of the managers creates a schedule in Project 2010 and saves it in the "Open Projects" subfolder. Our General Superintendent (not computer-savvy) needs to be able to pull all the schedules into one master project so he can view all of the projects together and plan manpower accordingly. My plan is to have a blank "Master" schedule in the "Schedules" folder that he will use to view all open projects as subprojects. I need help with the code that will make the following possible:
1. He opens "Master Schedule"
2. On open, any previous data in "Master Schedule" is cleared
3. After clearing "Master Schedule" all projects in "Schedules\Open Projects" are imported as subprojects of "Master Schedule"

I would like them linked so that he can make adjustments from the master schedule.

I'm pretty familiar with VBA, but I believe this requires use of Loops, which are not my strong suit. Any help would be greatly appreciated. Than ks!

Isaac

I can't help with MSProject but with loops:

To loop thru files in a folder

Code:

---

Dim PrjName As String

PrjName = Dir(Path & "\*.prj") 'Path must be end with, or be followed by, a backslash.
Do while PrjName <> ""
'PrjName includes the path
'do stuff

'Get next file name
Prjname = Dir
Loop

---

To loop thru all open Projects

Code:

---

Dim Prj As Object
For Each Prj in Ptojects
If not Prj.Name = "Master Project" then
'do stuff
End If
Next Prj

---

Thanks, SamT. That got me going in the right direction. Here's what I came up with (I used your basic method, but tweaked it to be consistent with other coding I've done). Apologies for not figuring out how to get the code in the code windows like you did:

Sub InsertSubProjects()

'Declare the variables
Dim projPath As String
Dim projFile As String

'Specify the path
projPath = "C:\Users\isaacc\Documents\Schedules\Open_Jobs\"

'Get the first file
projFile = Dir(projPath & "*.mpp", vbNormal)


Do While Len(projFile) > 0

ConsolidateProjects Filenames:=projFile, NewWindow:=False, HideSubtasks:=True

'Get the next file
projFile = Dir

Loop

End Sub



This SHOULD work. I know this because when it didn't, I changed the ConsolidateProjects line to a MsgBox projFile, and the routing would iterate through a message box for each project in the folder. When I run the code with the ConsolidateProjects line and the actual path and name of the file contained in quotes, it works fine. (The ConsolidateProjects line was copied from a macro I recorded to see the syntax.) But when I use the projFile variable for Consolidate Projects, I get the following error msgbox pop up for each file in the folder, and none of the files are pulled into the master schedule as subprojects.

Attachment 15356

So the best I can guess is that either Project doesn't like variables for subprojects, or There's another step or work-around for the issue.

C&P From the help file

Quote:

---

ConsolidateProjects Method


Displays the data from one or more projects in a single window.

Syntax:
expression.ConsolidateProjects(Filenames, NewWindow, AttachToSources, PoolResources, HideSubtasks, openPool, UserID, Password)

• expression Optional. An expression that returns an Application object.
• Filenames Optional String. One or more file names of projects to consolidate.
• NewWindow Optional Boolean. True if projects are inserted (consolidated) into a new project. False if projects are inserted into the active project at the selection point. The default value is False.
• AttachToSources Optional Boolean. True if changes in the consolidated project affect source projects. The default value is True.
• PoolResources Due to changes in the object model of Microsoft Project, this argument is ignored. It is retained so that existing macros which make use of this argument do not cause errors.
• HideSubtasks Optional Boolean. True if the subtasks of the projects specified with Filenames are hidden. The default value is True.
• openPool Optional Long. The action to take when opening a resource pool or sharer file. When opening a master project, the value for this argument is also applied to the subprojects. Can be one of the following PjPoolOpen constants: pjDoNotOpenPool, pjPoolAndSharers, pjPoolReadOnly, pjPoolReadWrite, or pjPromptPool. The default value is pjPromptPool.
• UserID Optional String. A user ID to use when accessing a project in a database. If Filenames does not refer to a database, UserID is ignored.
• Password Optional String. A password to use when opening password-protected project files. If Password is incorrect or omitted and a file requires a password, the user is prompted for the password.

Remarks
To specify that a consolidated project should be inserted as read-only, type (R/O) at the end of the file name in Filenames.

If the ConsolidateProjects method is used without specifying any arguments, the Insert Project dialog box appears.

---

If a Parameter is not specified, the Default value is used.

Code:

---

MasterProject.ConsolidateProjects(Filenames:=projFile)

---

That is equivalent to

Code:

---

MasterProject.ConsolidateProjects(Filenames:=projFile, NewWindow:=False, _
 AttachToSources:=True, PoolResources:=Null, HideSubtasks:=True, _
openPool:=pjPromptPool, UserID:="", Password:="")

---

Thank you. Where did the MasterProject come from? Is this a variable I need to Dim and declare?

I just used MasterProject as a placeholder.. I imagine that MS Project uses the File name as the Project Name. Assuming that the file you are making for your boss is named Master.mpp then

Code:

---

Dim MasterParoject As Project
Set MasterProject = Projects("Master.cpp")

'start of dir code
'
'
MasterProject.ConsolidateProjects(Filenames:=projFile)
'dir code
loop

---

Somewhere on the internet I found a downloadable copy of vbapj10.chm, the MS Project help file. That is what I have been referencing in this thread, because I don't have MS Project.

Thanks for your all your help, SamT. I appreciate it. Nothing seems to work though. I'm guessing that Project just isn't set up to work this way.

I spoke too soon. I tried recording the macro differently. The problem wasn't in the Consolidate code, it was because I was trying to import the subproject to the same row each time. I tried the following and although I need to tweak it because it leaves empty spaces between the subprojects, it still imports them all.

Code:

---

Sub InsertSubProjects()

    'Declare the variables
    Dim projPath As String
    Dim projFile As String
    Dim r As Long
       
    'Specify the path to the folder
    projPath = "C:\Users\isaacc\Documents\Schedules\Open_Jobs\"
   
    'Get the first file from the folder
    projFile = Dir(projPath & "*.mpp", vbNormal)
    r = 1
   
    Do While Len(projFile) > 0
        'MsgBox projFile
       
        SelectTaskField Row:=r, Column:="Name"
        ConsolidateProjects Filenames:=projFile, NewWindow:=False, HideSubtasks:=True
       
        'Get the next file from the folder
        projFile = Dir
        r = r + 1
   
    Loop

End Sub

---

OK. Final solution:

Code:

---

Sub InsertSubProjects()

    'Declare the variables
    Dim projPath As String
    Dim projFile As String
    Dim r As Long
       
    'Specify the path to the folder
    projPath = "C:\Users\isaacc\Documents\Schedules\Open_Jobs\"
   
    'Get the first file from the folder
    projFile = Dir(projPath & "*.mpp", vbNormal)
    r = 1
   
    Do While Len(projFile) > 0
        'MsgBox projFile
       
        ConsolidateProjects Filenames:=projFile, NewWindow:=False, HideSubtasks:=True
        SelectTaskField Row:=1, Column:="Name"
       
        'Get the next file from the folder
        projFile = Dir
       
   
    Loop

End Sub

---

It turns out that the row reference is relative to the last row with a task. The code above took care of the issue. Thanks for the help.

OK so another weird thing. The code doesn't work...UNLESS I first record a macro of inserting a subproject - just one is fine. After I stop recording the macro, the code for inserting all the subprojects works just fine. Over and over - at least until I close the file and open it again. Then I have to record a macro to get it to start working again.

I suspect that this is not consolidating to the project you think it is

Code:

---

ConsolidateProjects Filenames:=projFile, NewWindow:=False, HideSubtasks:=True
        SelectTaskField Row:=1, Column:="Name"

---

Ii is not consolidating TO a specific project. ConsolidateProjects is a method. without an Object being specified, it is probably being applied to the Application Object.

This consolidates TO the Master.cpp Project (See Post #6)

Code:

---

MasterProject.ConsolidateProjects(Filenames:=projFile)

---

The object here is the specific Project Object.

Record these two macros and share the results with me.

Quote:

---

1. In Project, open the files that you want to consolidate
2. On the Window menu, click New Window.
3. In the Projects list, click the file that you want to list first when you combine the projects.
4. To select adjacent projects, hold down SHIFT, and then click the first and last project that you want to list in the consolidated project. To select nonadjacent projects, hold down CTRL, and then click the name of the next file that you want to list in the consolidated project.
5. Repeat this step until you have selected all the files that you want to consolidate.

---

Quote:

---

On the Insert menu, click Project. Select the files that you want to consolidate, and then click Insert.

---

See Also:
http://www.epmcentral.com/articles/p...terproject.php
http://www.mpug.com/articles/ask-the...ross-projects/

So I tried the code in Post #6 last night. But I didn't share the results. I get the following error: Object doesn't support this property or method.

I have Project 2010, so the methods in Post #11 aren't available per se. The first one, I can't figure out an alternate (there is no "Window" menu, and when I open a new Project, I don't see a Projects list anywhere, unless I go to File>Recent. But that just allows me to go to one of the open projects, not select any of them.

There is no insert menu, but from what I understand, this was replaced in 2010 with the Subprojects on the Projects tab.This is what I've been using to figure things out so far. Here is what it comes up with.

Here's what happens when I Ctrl+A to select all files in folder to insert as subprojects:

Code:

---

Sub Macro6()
' Macro Macro6
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150203.mpp,C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150175.mpp,C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150181.mpp,C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150014.mpp,C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150025.mpp,C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150171.mpp", NewWindow:=False, HideSubtasks:=True
End Sub

---

Here's what hapens when I select one project at a time to insert:

Code:

---

Sub Macro8()
' Macro Macro8
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150146.mpp", NewWindow:=False, HideSubtasks:=True
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150038.mpp", NewWindow:=False, HideSubtasks:=True
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-160013.mpp", NewWindow:=False, HideSubtasks:=True
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150183.mpp", NewWindow:=False, HideSubtasks:=True
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150163.mpp", NewWindow:=False, HideSubtasks:=True
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150158.mpp", NewWindow:=False, HideSubtasks:=True
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150106.mpp", NewWindow:=False, HideSubtasks:=True
End Sub

---

That method inserts subprojects in the first row and just moves al the prior inserted projects down. So here's the last method, which moves to the next row after each insertion("Indicators" is just the first column - in Excel it would correspond to the "A" in "A1"):

Code:

---

Sub Macro10()
' Macro Macro10
    SelectTaskField Row:=1, Column:="Indicators", RowRelative:=False
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150014.mpp", NewWindow:=False, HideSubtasks:=True
    SelectTaskField Row:=2, Column:="Indicators", RowRelative:=False
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150025.mpp", NewWindow:=False, HideSubtasks:=True
    SelectTaskField Row:=3, Column:="Indicators", RowRelative:=False
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150038.mpp", NewWindow:=False, HideSubtasks:=True
    SelectTaskField Row:=4, Column:="Indicators", RowRelative:=False
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150106.mpp", NewWindow:=False, HideSubtasks:=True
    SelectTaskField Row:=5, Column:="Indicators", RowRelative:=False
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150146.mpp", NewWindow:=False, HideSubtasks:=True
    SelectTaskField Row:=6, Column:="Indicators", RowRelative:=False
    ConsolidateProjects Filenames:="C:\Users\isaacc\Documents\Schedules\Open_Jobs\IRC-150158.mpp", NewWindow:=False, HideSubtasks:=True
End Sub

---

Perhaps important is the following: all macros are recorded into the Global template, not the project that I'm working in when I record them.

Similar to Post #10, if I manually insert a project, the code works fine until I close and re-open the file. I'm thinking that there is a property or something that is set by clicking the actual Subproject button that identifies the open projects as a "master" project.

Global Template sounds like the Personal Excel file and the DotNormal Word File. IOW, IF you have it, then it is open every time the Application is opened and all its procedures are available via calling from another file or from the Macros menu.

I use the Excel Personal.xls file to hold code that I often paste is other workbooks and a few, like ShowAllSheets and SetZoom100 and SetZoom75 for when I am working on other people's workbooks, but I don't keep any production Code in it. Code I don't want to show in the Macros Menu is in a Module that has "Option Private Module" at the top. This also means that no code in any other file can call those procedures.

In my imagination I see that MS Project has four classes of Files, Global Template, Project Templates, Projects, and SubProjects. I may be wrong about the second: Project Templates.

SideBar:

Quote:

---

I was a contractor for many years until I caught Cancer. While recovering, I learned VB For Excel to develop Business Systems for my business. I never recovered to the point of continuing in business, but I still love VBA.

IF I was still a contractor and using MS Project, only the VBA Developer, that's you and me, would have a Global Template. There would be a Project Master Template that all PMs would have to use for their Projects, so that all would have the same look and feel. This Master Template would also have all resources and their scheduled work time in it do prevent cross scheduling. This system is needed because while J.Smyth and J.Smith may be the same person they would be used in any Master schedule as two different Resources.

I would also have a Master Project, also based on the Master Template, with all other projects as SubProjects, so that all other Projects could have the Resources coordinated.

Quote:

---

You said: Our General Superintendent (not computer-savvy) needs to be able to pull all the schedules into one master project so he can view all of the projects together and plan manpower accordingly. My plan is to have a blank "Master" schedule in the "Schedules" folder that he will use to view all open projects as subprojects.

---

From that, I am inferring that the PM's Projects are basically Gantt Charts and are not scheduling Resources.

---

If you put the code for the Super's Master Project in the MasterProject file, you should be able to use ConsolidateProjects as a stand alone Method.

Code:

---

ConsolidateProjects Filenames:=projFile, NewWindow:=False, HideSubtasks:=True
SelectTaskField Row:=1, Column:="Name"

---

If you keep it in the Global Templates File you will need to use

Code:

---

MasterProject.ConsolidateProjects(Filenames:=projFile)

---

As I described previously, but you, I mean the Super, will need to run the Procedure from the Mcros menu or you will need to code a button in the Master Project

Please bear with me, I am learning the quirks of VBA for MS project as we go.