Consulting

Results 1 to 15 of 15

Thread: VBA to use all projects in folder as subprojects

  1. #1
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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.

    Capture.JPG

    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.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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:="")
    Last edited by SamT; 02-05-2016 at 10:48 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    Thank you. Where did the MasterProject come from? Is this a variable I need to Dim and declare?

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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.

  8. #8
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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
    Last edited by SamT; 02-06-2016 at 05:29 AM.

  9. #9
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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.
    Last edited by SamT; 02-06-2016 at 05:31 AM.

  10. #10
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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.

  11. #11
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    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.
    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/
    Last edited by SamT; 02-06-2016 at 06:20 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  12. #12
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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
    Last edited by SamT; 02-06-2016 at 08:54 AM.

  13. #13
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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.

  14. #14
    VBAX Regular
    Joined
    Dec 2015
    Posts
    11
    Location
    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.

  15. #15
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •