PDA

View Full Version : VBA to use all projects in folder as subprojects



Mr_IC
02-03-2016, 07:42 PM
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

SamT
02-03-2016, 08:24 PM
I can't help with MSProject but with loops:

To loop thru files in a folder

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

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

Mr_IC
02-05-2016, 10:06 AM
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.

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.

SamT
02-05-2016, 10:38 AM
C&P From the help file
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.


MasterProject.ConsolidateProjects(Filenames:=projFile)
That is equivalent to

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

Mr_IC
02-05-2016, 11:56 AM
Thank you. Where did the MasterProject come from? Is this a variable I need to Dim and declare?

SamT
02-05-2016, 06:24 PM
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

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.

Mr_IC
02-05-2016, 08:30 PM
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.

Mr_IC
02-05-2016, 08:41 PM
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.


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

Mr_IC
02-05-2016, 08:49 PM
OK. Final solution:


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.

Mr_IC
02-05-2016, 09:25 PM
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.

SamT
02-06-2016, 05:57 AM
I suspect that this is not consolidating to the project you think it is

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)

MasterProject.ConsolidateProjects(Filenames:=projFile)
The object here is the specific Project Object.

Record these two macros and share the results with me.



In Project, open the files that you want to consolidate
On the Window menu, click New Window.
In the Projects list, click the file that you want to list first when you combine the projects.
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.
Repeat this step until you have selected all the files that you want to consolidate.



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/ps07masterproject.php
http://www.mpug.com/articles/ask-the-experts-combining-resources-across-projects/

Mr_IC
02-06-2016, 07:52 AM
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:


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:


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"):


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

Mr_IC
02-06-2016, 07:54 AM
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.

Mr_IC
02-06-2016, 08:08 AM
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.

SamT
02-06-2016, 10:07 AM
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:
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.


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.
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
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.