none
Dynamically Linking Subprojects and Updating Notes

    Pertanyaan

  • Short Version

    1. How can I link subprojects and execute VBA on multiple levels without getting a bunch of "Enable Macro" prompts?
    2. How can I manipulate a subproject using the VBA of a parent or parent of a parent project?
      • If this works, it may change what I'm doing for the first question

    Long Version

    So, here's the situation:

    I'm creating a management form to simplify the creation/updating of project files. I have the form created and am able to generate new project files accordingly. What I'm having trouble with is storing the form data and linking the subprojects in a dynamically robust manner.

    I have a network drive where the MPP files are stored. The structure is as follows:

    .\
    ├MasterTracker.mpp
    ├\Projects
    │├\2018
    ││├Proj1.mpp
    ││├Proj2.mpp
    ││├Proj3.mpp
    ││└Proj4.mpp
    │└2018.mpp
    └\Templates
     ├Project.mpp
     └Year.mpp

    The idea is that this single tracker will be useable across multiple years and the projects will be organized as such.

    Here's where my trouble comes in.

    1. Using the follow VBA, I'm able to dynamically link all the yearly projects when the MasterTracker.mpp is opened:

      ThisProject

      Option Explicit
      
      Private Sub Project_Open(ByVal pj As Project)
          projPath = Application.ActiveProject.Path & "\Projects\"
          tempPath = Application.ActiveProject.Path & "\Templates\"
      
          Call buildList
      End Sub

      Module1

      Public projPath As String
      
      Public Sub buildList()
          Dim fs As Object
          Dim projFile As String
      
          Set fs = CreateObject("Scripting.FileSystemObject")
          projFile = Dir(projPath & "\*.mpp", vbReadOnly)
      
          Do While Len(projFile) > 0
              If fs.GetBaseName(projFile) <> "Template" Then
                  ConsolidateProjects Filenames:=projPath & "\" & projFile, NewWindow:=False, HideSubtasks:=True
                  SelectTaskField Row:=1, Column:="Name"
              End If
      
              projFile = Dir
          Loop
      
          Dim tsk As Task
      
          For Each tsk In ActiveProject.Tasks
              If tsk.Subproject <> "" Then
                  Debug.Print projPath & fs.GetBaseName(tsk.Subproject) & "\"
                  FileOpen Name:=tsk.Subproject, ReadOnly:=True
              End If
          Next tsk
      End Sub

      In the Year.mpp file is similar code for linking the projects for the corresponding year. This works, even in Read-Only, however, there are quite a few warnings that pop-up (i.e., the "Enable Macros" box). I'd prefer to avoid these prompts, if possible.

    2. Secondly, the following code is being used to create the individual project files and put them in the correct location:

      manageProject (Form)

      Private Sub submit_Click()
          ' Validate
          Dim valid As Boolean
          valid = False
      
          ' Add Validation Here
      
          valid = True ' Remove after validation is setup
      
          If valid Then
              ' Format Form in JSON
              Dim data As Dictionary
              Set data = New Scripting.Dictionary
              With data
                  .Add "courseProjectSelector", courseProjectSelector.Value
                  .Add "courseName", courseName.Value
                  .Add "program", program.Value
                  .Add "priority", priority.Value
                  .Add "developmentType", developmentType.Value
                  .Add "projectDescription", projectDescription.Value
                  .Add "technology", technology.Value
                  .Add "ieQep", ieQep.Value
                  .Add "customMultimedia", customMultimedia.Value
                  .Add "textbooks", textbooks.Value
                  .Add "needDate", needDate.Value
                  .Add "startDate", startDate.Value
                  .Add "deliverableDate", deliverableDate.Value
                  .Add "dueDate", dueDate.Value
                  .Add "status", status.Value
                  .Add "notes", notes.Value
                  .Add "sme", sme.Value
                  .Add "smeCampus", smeCampus.Value
                  .Add "assignedID", assignedID.Value
                  .Add "loadCDA", loadCDA.Value
                  .Add "qaCDA", qaCDA.Value
                  .Add "completionDate", completionDate.Value
                  .Add "acnNo", acnNo.Value
                  .Add "authorization", authorization.Value
                  .Add "adjustReimbursement", adjustReimbursement.Value
              End With
      
              Dim fs As Object
              Dim newProj As String
              Set fs = CreateObject("Scripting.FileSystemObject")
              newProj = projPath & Format(needDate.Value, "yyyy") & "\" & courseProjectSelector.Value & ".mpp"
      
              Debug.Print tempPath & "Project.mpp"
              fs.CopyFile tempPath & "Project.mpp", newProj
      
              ' Add new project to displayed year without having to reopen (how?)
      
              ' Add form data to project file in notes field (how?)
          End If
      End Sub

      I think the notes at the bottom of the sub speaks for themselves. I know how to use the VBA to add the subprojects when the files are initially loaded, but not how to manipulate them once they already been loaded. Then there is the question of how to store that form data. In another question, it was suggested I set it to the Notes field, but I don't know how to add data to another file and I'm having a really hard time figuring out anything meaningful from the MSDN documentation. It seems significantly less informational than all the other Office applications

    08 Mei 2018 17:57

Jawaban

  • Angel,

    Although I'd love to work on all the details of your issue I don't have the time right now. However, perhaps I can give you some initial guidance.

    I believe the "Enable macro" message you are getting is because your trust center macro security setting is to tight. I always operate with my setting at "Enable all macros". So take a look at that.

    As far as manipulating data in subprojects, this is how you can do it. Let's say your macro is either run from the master (resident in the master) or run on the master (macro in Global), then to access a task line in the first subproject use the following:

    ActiveProject.Subprojects(1).SourceProject.Tasks(2).Name = "First level task"

    You can index through each subproject and you should also be able to bore down to any level of insertion level with this syntax.

    I note one of your goals is to manipulate subprojects in a "dynamically robust manner". Sorry, but that's a tough one since linked structures in Project are prone to corruption. None of the files in the structure should ever by renamed, moved, overwritten or "saved off" to another location. Ideally all files should reside in a single directory on a local drive and not be operated over a network, networks are prone to glitches.

    Hopefully this provides some help.

    John

    08 Mei 2018 19:45

Semua Balasan

  • Angel,

    Although I'd love to work on all the details of your issue I don't have the time right now. However, perhaps I can give you some initial guidance.

    I believe the "Enable macro" message you are getting is because your trust center macro security setting is to tight. I always operate with my setting at "Enable all macros". So take a look at that.

    As far as manipulating data in subprojects, this is how you can do it. Let's say your macro is either run from the master (resident in the master) or run on the master (macro in Global), then to access a task line in the first subproject use the following:

    ActiveProject.Subprojects(1).SourceProject.Tasks(2).Name = "First level task"

    You can index through each subproject and you should also be able to bore down to any level of insertion level with this syntax.

    I note one of your goals is to manipulate subprojects in a "dynamically robust manner". Sorry, but that's a tough one since linked structures in Project are prone to corruption. None of the files in the structure should ever by renamed, moved, overwritten or "saved off" to another location. Ideally all files should reside in a single directory on a local drive and not be operated over a network, networks are prone to glitches.

    Hopefully this provides some help.

    John

    08 Mei 2018 19:45
  • Thank you so much for the guidance.

    I'm afraid I can't do anything about the Trust Center settings as the entire page in the settings is disabled, even when I launch the program with my administrative account. It's the same in all of our Microsoft Office programs. My best guess is that it's a network policy, but that's a level of administrative work I general don't deal with.

    With regards to how to manage the subprojects, that is so much more helpful than anything I've found. I feel completely blind that I missed the SourceProject property in the MSDN documentation. Combined with the Path property, I should be able to eliminate the need for VBA in the yearly MPPs, leaving them as just placeholders to the subprojects of each year.

    As for your concern about corruption and manipulating the files via VBA, I hope to limit that manipulation, who can make them, and make regular backups. The main purpose of this is to provide a single, centralized point for dictating and monitoring projects. It is only expected to copy the template file(s) as needed and up keep the overall project data current. If I could avoid all this, I gladly would. Sadly, I don't get to decide how we do things; I just make it so it can be done (whenever possible).

    I'm going to see what I can work out, tomorrow. Hopefully, that will be all I need to finally breakthrough and wrap this project up, but, if not, I'll be back. Or I'll be marking your response as the answer; either way I'll be back.

    09 Mei 2018 0:01
  • John,

    Would I be able to somehow use this with ConsolidateProjects? I don't think I can, since it's a method of Application, but I figure I'm working enough out of my comfort zone to ask. ^^'

    How this applies is that I use a form to create a new project. The file is created in the correct location, no problem, but when I go to add the file to the list, it is added to the MasterTracker, instead of the intended year.

    15 Mei 2018 19:21
  • Angel,

    The ConsolidateProjects method is simply the VBA equivalent of going to Project > Insert group > Subproject so I'm not sure how that would be of any help since you already have a "master traskcer", but then I don't fully understand what you are doing anyway.

    John

    16 Mei 2018 1:56
  • John,

    Thank you for all of your help, thus far. I really appreciate it! This type of programming isn't my area of expertise. ^^'

    Basically, I need to have ConsolidateProject insert the new project as a subproject of the appropriate year, but I only seem be able to get it to insert directly into the MasterTracker. So, instead of a new project being listed under the 2018 subproject of the MasterTracker, it lists directly under the MasterTracker.

    16 Mei 2018 12:54
  • Angel,

    You can insert a new subproject anywhere in an existing project. First select the point of insertion as the year subproject of you MasterTracker. You can do the selection with code. Then when you execute the ConsolidateProjects Method, set the NewWindow argument to false. The new file will be inserted at the selected insertion point.

    Hope this helps.

    John

    16 Mei 2018 13:44
  • John,

    I see, that works for fixed placements where I select the a child of the subproject or the first row after with the subproject expanded, but how can I identify the appropriate row from the subproject name? I can't find anything that would allow me to do so.

    ' Determine New or Existing Dim existing As Boolean existing = False ' Add logic here ' existing = True ' Remove after validation is setup Dim projYear As String Dim tarSubProj As Project projYear = Format(needDate.Value, "yyyy") If existing = False Then ' Create New Project File Dim fs As Object Dim newProj As String Dim subProj As Project Set fs = CreateObject("Scripting.FileSystemObject") newProj = projPath & projYear & "\" & courseProjectSelector.Value & ".mpp" ' Copy the template into the new project Debug.Print tempPath & "Project.mpp" fs.CopyFile tempPath & "Project.mpp", newProj ' Link the new project into the appropriate subproject For Each subProj In ActiveProject.Subprojects If fs.GetBaseName(subProj.Path) = projYear Then ' Expand this row, if not already expanded ' Select one row below this and insert ' Collapse this row, if it was originally collapsed Exit For End If Next subProj End If



    16 Mei 2018 14:21
  • Angel,

    Remember that a selection object is foreground processing, that is, it is dependent on the active view. However since all the select methods require a view row number you need to get creative in identifying that row. Here's a trick you can use.

    Use a flag field (e.g. Flag1) to identify where you want to insert the new subproject. Set the flag for the desired row and the one above it. Now apply a filter such that the only two rows in the view are those you just set. Insert the new subproject at row 2. Finally remove the filter.

    Sometimes you just gotta punt. I've been at this a while.

    John

    16 Mei 2018 15:44
  • Definitely had to get creative, but because there's not any VBA in any other files, I could do it without prompts!

            ' Determine New or Existing
            Dim existing As Boolean
            existing = False
    
            ' Add logic here
    
    '        existing = True ' Remove after validation is setup
    
            Dim projYear As String
            projYear = Format(startDate.Value, "yyyy")
    
            If existing = False Then
                ' Create New Project File
                Dim fs As Object
                Dim newProj As String
                Dim subProj As Subproject
                Set fs = CreateObject("Scripting.FileSystemObject")
                newProj = projPath & projYear & "\" & courseProjectSelector.Value & ".mpp"
    
    
                Debug.Print tempPath & "Project.mpp"
                fs.CopyFile tempPath & "Project.mpp", newProj
                Debug.Print tempPath
    
                For Each subProj In ActiveProject.Subprojects
                    If fs.GetBaseName(subProj.path) = projYear Then
                        ' Open year project
                        FileOpenEx Name:=subProj.path
    
                        ' Select the last row
                        Dim found As Boolean
                        found = False
    
                        SelectTaskField Row:=1, Column:="Name", RowRelative:=False
                        Do While ActiveCell <> ""
                            SelectTaskField Row:=1, Column:="Name"
                        Loop
    
                        ' Insert new project
                        ConsolidateProjects Filenames:=newProj, NewWindow:=False, HideSubtasks:=True
    
                        ' Save and close year project
                        FileCloseEx Save:=pjSave
                        Exit For
                    End If
                Next subProj
            End If

    There is a problem, though. The tempPath and projPath are being declared when the MasterTracker is first loaded and are Public so they can be used by the entire project, but their values are being lost whenever there is an execution error, resulting in my having to fix the error, close the MasterTracker, and reopen it.

    Any ideas on how I might get around having to reopen the file or declare the paths in every sub or function that might use them? I know that VBA isn't the most stable programming language to execute, so even after it is fully functional, I expect there will be some errors popping up.

    16 Mei 2018 20:17
  • Angel,

    Sorry I don't understand your first sentence at all (sentence before the code).

    You appear to be declaring variable just before they are used. Not a good programming practice. I declare all variables at the very beginning. If there are multiple procedures within a module I use a Public declaration. The only time I may declare variables locally is if they are only used in a subroutine (e.g. temporary variables).

    Yes it can be a pain to troubleshoot when working with linked structures (e.g. master/subprojects) as you often have to close out and start over after fixing a problem. However, I'm not sure where you got the idea that VBA is not stable. Once the code is operational I find it to be extremely reliable. I've got macros I wrote for Project 97, which still work with current versions of Project. And one of my macros that runs on a linked structure only had to be modified once when Microsoft changed the file format structure going from Project 2007 to Project 2010.

    If you're still getting errors popping up then your code hasn't been adequately rung out.

    John

    16 Mei 2018 22:20
  • My apologies for the confusion. The code was intended to show you the solution I came up with to link the new project to a subproject of the MasterTracker. I believe this project is almost completely flushed out, and it's thanks to your guidance!

    As for the variable declarations, the overall code is a bit more elaborate than what I've shared and I'm certain I've missed a few details as I've pulled out the relevant segments. Those that are used throughout the code are declared when the MasterTracker is opened and available globally as Public. They work fine until there's an error, then they reset.

    Anyways, putting that aside, I am having trouble with making changes to the subprojects. Obviously, I have linking working fine, and I have the title/name of the project updating, but I can't seem to get the notes to update. I don't get any errors, the data just doesn't change.

            For Each subProj In ActiveProject.Subprojects
                If fs.GetBaseName(subProj.path) = projYear Then
                    Dim projName As String
                    projName = courseProjectSelector.Value & " " & courseName.Value
                    
                    ' Open year project
                    FileOpenEx Name:=subProj.path
    
                    ' Find and update project in projects
                    Dim proj As Subproject
                    For Each proj In subProj.SourceProject.Subprojects
                        If fs.GetBaseName(proj.path) = courseProjectSelector.Value Then
                            ' Open the target project
                            FileOpenEx Name:=proj.path
    
                            ' Update the target project
                            ProjectSummaryInfo Title:=projName
                            Debug.Print JsonConverter.ConvertToJson(data) ' Added only to confirm the string is generated properly and without issue [Remove When Done]
                            ActiveProject.ProjectNotes = JsonConverter.ConvertToJson(data)
                            Debug.Print ActiveProject.ProjectNotes ' Added only to confirm the notes have been updated with the JSON string [Remove When Done]
    
                            ' Save and close target project
                            FileCloseEx Save:=pjSave
                        End If
                    Next proj
    
                    ' Find and update project in tasks
                    SelectTaskField Row:=1, Column:="Name", RowRelative:=False
                    Do While ActiveCell <> ""
                        If fs.GetBaseName(ActiveCell.Task.Subproject) = courseProjectSelector.Value Then
                            ActiveCell.Task.Name = projName
                            Exit Do
                        End If
                        SelectTaskField Row:=1, Column:="Name"
                    Loop
    
                    ' Save and close year project
                    FileCloseEx Save:=pjSave
                    Exit For
                End If
            Next subProj

    I'm using [VBA-JSON](https://github.com/VBA-tools/VBA-JSON) to convert the form data into a JSON string, which I'm trying to store with ActiveProject.ProjectNotes = JsonConverter.ConvertToJson(data), but the notes aren't being saved. When I then do the Debug.Print immediately after, it returns nothing.

    17 Mei 2018 18:31
  • Angel,

    I've never worked with JSON so I'm not familiar with the string formatting. I took a look at the reference but I don't have time to figure out the what and why.

    What exactly is your "form data"? Why do you need to convert it into JSON? Visual Basic code and Project's Notes field uses standard ASCII notation.

    John

    17 Mei 2018 21:04
  • I'm using the JSON for ease of parsing and my familiarity with it, but the problem is really my inability to set the notes. I've tried setting it to a simple, short, fixed string, and it doesn't save using ActiveProject.ProjectNotes = "String" at the same point where I can change the project title.
    18 Mei 2018 0:12
  • Angel,

    The ActiveProject.ProjectNotes Property sets the Comments field of the Project Properties. My guess is, it is working but you just aren't looking in the right place. Where exactly do you want the note text to appear? It can be the Comments of the Project Properties, it could be the Notes field of the Project Summary Task, or it could be the Notes field for any task, resource or assignment, they are all separate independent fields.

    John

    18 Mei 2018 13:57
  • John,

    The values need to be stored in the individual project files, so the data is always with them.

    With regards to where the data is stored, I have checked Comments block of the project file after the update was made via VBA, and it was empty, just like when I did the Debug.Print of the value.

    18 Mei 2018 14:42
  • Angel,

    I'd have to run some tests on the code you last posted to see what may be going wrong but unfortunately I don't have time to do that. I did a quick test on a dynamic master test file I have and the following syntax did put the desired string in the Comments field of the first subproject.

    ActiveProject.Subprojects(1).SourceProject.ProjectNotes = "go"

    And just as an observation, of all the discussion we've had over the past several days and all the help I've given you, I have yet to see any "votes" or "marked as answer"

    John


    18 Mei 2018 15:53
  • Sorry for the lack of votes, I generally am too distracted by the topic to think of them. I've gone back and upvoted all your posts, they've all been helpful in pursuing this. As for the marked as answered, I didn't want to mark it until it was done. You've been amazing at helping me, but I am still working on the updating of the subprojects.

    I just tried changing it so that the notes are added via the SourceProject property, and the Debug.Print show it as having been updated, but when I checked the actual project file via the UI or ran a Debug.Print on the value from within the file, it came up empty.

                            subProj.SourceProject.ProjectNotes = "Pie"
                            Debug.Print subProj.SourceProject.ProjectNotes ' Returned: Pie

    Seems so odd to me that it would act like that, giving the appearance and initial success, only for it apparently not save. I know it is saving the file, though, as the change for the title/name is being retained.

    18 Mei 2018 17:36
  • I figured out a solution to this ProjectNotes updating. The update code, itself, appears to have been working, it just wasn't saving. Odd considering I was updating the Title in the same block and it saved, but I added an extra step to save the project before closing and that appears to have done the trick.

    ActiveProject.SaveAs Name:=ActiveProject.path & "\" & ActiveProject.Name
    FileCloseEx Save:=pjSave

    With this, I believe I've dealt with all the major issues concerning this portion of the project. Thank you, again, for all your help. I'll mark your initial post as the answer, John.

    21 Mei 2018 14:41
  • Angel,

    You're welcome and thanks for the feedback. I'm glad you figured it out.

    John

    22 Mei 2018 14:06