none
Possible to update one project from another using VBA? RRS feed

  • Question

  • Hello

    I have a summary project plan that I currently update by opening six different workstream plans, writing key milestone information to an Excel file and then updating the summary project from the Excel file.

    My question is - how to do it without using Excel?  Can I keep multiple projects open and update the summary project on the fly?

    HL Example:

    Dim sProj As Project    'Summary project
    Dim wProj As Project   'Multilple Workstream projects
    Dim wProjectFiles(1 To 6) As String

    Set sProj = ActiveProject      'Run macro from summary project

    For X = 1 To 6  'list of workstream files
            FileOpen ' wProjectFiles
            Set wProj = ActiveProject

            For Each Tsk In wProj.Tasks
                If ' check if key milestone, write date and milestone code to variables
                    sProj.Activate    'now scroll through summary project plan and update the corresponding milestone
                    For Each Tsk In sProj.Tasks 
                        If Tsk.Number## = milestone code then update date
                        End if
                    Next
                    wProj.Activate   'revert to workstream plan 
                End if     

            Next

    Next

    Will this work and will project remember where it was in the first task loop if switching to another task loop?

    Thanks for any guidance....
    Art

    Wednesday, June 17, 2015 5:08 PM

Answers

  • Art-PMO,

    I didn't actually implement code to do what you want (I normally do and test it before posting) but here is a suggestion. You don't need to actually active each subproject to read or write data from/to it, so instead of activating each subproject try looping through all the subprojects using something like: (Note, this assumes only the summary project and the workstream projects are open but only the summary project is active. You wouldn't even need to have the workstream project open, you could reference them by full name which includes the path).

    Sub ReadWrkStreamData()
    Dim sProj As Project
    Dim prj As Project
    Dim t As Task
    Set sProj = ActiveProject
    For Each prj In Application.Projects
        If prj.Name <> "summary project" Then
            For Each t In prj.Tasks
                'find desired data
                sProj.Tasks.UniqueID(x).Start   'where "x" is UID of milestone task in summary project
            Next t
        End If
    Next prj
    End Sub

    Hope this helps.

    John

    • Marked as answer by Art-PMO Thursday, June 18, 2015 1:55 PM
    Wednesday, June 17, 2015 10:17 PM

All replies

  • Art-PMO,

    The answers are, "yes" (it will work) and "yes" (done correctly, the code will "remember" where it is in a loop).

    Although I love creating VBA solutions, have you considered a non-VBA, non-dynamic master solution? Ordinarily a user might be tempted to create a dynamic master file with each of the workstream files as subprojects. That configuration does work and will automatically give you the updates you want but it is also prone to corruption if rigorous file maintenance is not observed. However, there is an alternate to a dynamic master and that is a static master. A static master is simply a snapshot in time of all the workstream files when the master is created and it is created the same way as a dynamic master except the option to "link to project" in the lower right corner of the Insert Project window is unchecked. The downside of a static master is that you need to create a new one each time you want to update it, but that is easily automated with a recorded macro.

    So, what do you want to do? Do you wish to pursue the VBA approach or are you game for a static master?

    John

    Wednesday, June 17, 2015 7:17 PM
  • Hi John,

    Thanks for your response.  We do have a dynamic master/sub-project integrated plan but I avoided including a summary project with links b/c we have 150 program milestones.  We are currently using a network shared folder setup - and when I posted here some weeks ago whether I should try to link 150 milestones the general consensus was no b/c of the corruption risk you mentioned. 

    We are moving to Project Online in a couple of months and I anticipate (hope) the implementation partner assists us in creating a better master/sub reporting solution.  Till then I was hoping to change the code to avoid an Excel application spawn problem I sometime run across.  I understand your suggestion for the static master but for the next 2 months I was hoping to just tweak my existing code.

    Do you think the psuedo-code above will work or does it require any changes?

    Thanks again for your help!
    Art

    Wednesday, June 17, 2015 9:17 PM
  • Art-PMO,

    I didn't actually implement code to do what you want (I normally do and test it before posting) but here is a suggestion. You don't need to actually active each subproject to read or write data from/to it, so instead of activating each subproject try looping through all the subprojects using something like: (Note, this assumes only the summary project and the workstream projects are open but only the summary project is active. You wouldn't even need to have the workstream project open, you could reference them by full name which includes the path).

    Sub ReadWrkStreamData()
    Dim sProj As Project
    Dim prj As Project
    Dim t As Task
    Set sProj = ActiveProject
    For Each prj In Application.Projects
        If prj.Name <> "summary project" Then
            For Each t In prj.Tasks
                'find desired data
                sProj.Tasks.UniqueID(x).Start   'where "x" is UID of milestone task in summary project
            Next t
        End If
    Next prj
    End Sub

    Hope this helps.

    John

    • Marked as answer by Art-PMO Thursday, June 18, 2015 1:55 PM
    Wednesday, June 17, 2015 10:17 PM
  • Terrific John - thanks I will try it...

    Regards
    Art

    Thursday, June 18, 2015 1:55 PM
  • Art,

    You're welcome and thanks for the feedback.

    John

    Thursday, June 18, 2015 2:25 PM
  • Hi again John,

    Sorry to ask but I'm having a little troube getting this part to work:

    "You wouldn't even need to have the workstream project open, you could reference them by full name which includes the path)."

    I tried the following but throwing an error:

    Dim prjWS As Project
    prjWS = "c:\test.mpp"
    For Each Tsk In prjWS.Tasks
        'test data
    Next 

    Any guidance?

    Thanks
    Art

    Thursday, June 18, 2015 8:49 PM
  • Art,

    You need the full path, not just "c:\test.mpp". One easy way to get the full path is to open a project file, then in the VB Editor show the Immediate Window. Type the following:

    Print ActiveProject.FullName

    That will give something like C:\Users\[computer name]\[folder]\filename

    Try that and if it still gives you problems, let me know but I'll be out of pocket for a couple of hours.

    John

    Thursday, June 18, 2015 9:39 PM
  • Ahh of course... got it now... the code works great - fast and efficient, much better than I was using before.

    Much appreciated John!
    Art

    Friday, June 19, 2015 11:34 AM
  • Art,

    Super! Don't you just love it what it all falls together.

    Happy coding.

    John

    Friday, June 19, 2015 2:34 PM