none
Looping through Master-Sub Projects extracting task usage data RRS feed

  • Question

  • Gary-

    I am using Project 2007 Pro to extract time phased task usage data from a Master-SubProject structure on a Project 2007 Server and write the data to an Excel workbook.

    I found some code on Jach Dahlgren's site and was able to modify it to make it work on a standalone test project running locally. I then tried running the code on the local test project with a subproject inserted. I have done some additional reading and understand that the subproject tasks live within the projects in the subprojects collection.

    I have Rod Gill's book VBA Programming for Microsoft Office Project and have his code for looping through the subprojects. I have not yet been able to insert this code into my working code.  

    I am now trying to figure out how to loop through my test project, write the lines that are in the master project above the inserted sub project, then loop through the subproject and get its tasks, then loop through a subproject inserted into the first subproject. Once I am done here, I come back to the master project and pick up a task that lives within the master, a "project complete" milestone. This right here is my biggest stumbling block as I am struggling to figure out how to traverse the hierarchy.

    If my understanding is correct, I would need to loop while moving up and down between levels in the subproject hiearchy to extract each task. My goal is to, in Excel, mirror the task usage view, with the cost table applied, and columns of monthly timephased data that mirrors the timephased grid. Like I said, I can make it work in a stand alone project, but not in a consolidated project.

    Any direction would be most appreciated.

    R. Day

    EVM Project Manager - Serco North America 

    Tuesday, May 11, 2010 6:15 PM

Answers

  • Hi,
     
    Firstly it's better not have any tasks "living" in the master file.
     
    Your structure will be something like:
     
    Sub test()
    Dim Tsk As Task
    Dim subTsk As Task
    Dim subproj As Project
        For Each Tsk In ActiveProject.Tasks
            If Tsk.Subproject <> "" Then
                FileOpen Name:=Tsk.Subproject, ReadOnly:=True
                For Each subTsk In ActiveProject.Tasks
                    'Process data
                Next subTsk
                FileClose pjDoNotSave
            Else
                'Process data
            End If
        Next Tsk
    End Sub

    --

    Rod Gill
    Microsoft MVP for Project - http://www.project-systems.co.nz
     
    Author of the only book on Project VBA, see: http://www.projectvbabook.com
     

     
    "Raymondd7" wrote in message news:16d8f4c8-4e52-43cc-9f3c-912fa841e4cd...

    Gary-

    I am using Project 2007 Pro to extract time phased task usage data from a Master-SubProject structure on a Project 2007 Server and write the data to an Excel workbook.

    I found some code on Jach Dahlgren's site and was able to modify it to make it work on a standalone test project running locally. I then tried running the code on the local test project with a subproject inserted. I have done some additional reading and understand that the subproject tasks live within the projects in the subprojects collection.

    I have Rod Gill's book VBA Programming for Microsoft Office Project and have his code for looping through the subprojects. I have not yet been able to insert this code into my working code.  

    I am now trying to figure out how to loop through my test project, write the lines that are in the master project above the inserted sub project, then loop through the subproject and get its tasks, then loop through a subproject inserted into the first subproject. Once I am done here, I come back to the master project and pick up a task that lives within the master, a "project complete" milestone. This right here is my biggest stumbling block as I am struggling to figure out how to traverse the hierarchy.

    If my understanding is correct, I would need to loop while moving up and down between levels in the subproject hiearchy to extract each task. My goal is to, in Excel, mirror the task usage view, with the cost table applied, and columns of monthly timephased data that mirrors the timephased grid. Like I said, I can make it work in a stand alone project, but not in a consolidated project.

    Any direction would be most appreciated.

    R. Day

    EVM Project Manager - Serco North America 



    __________ Information from ESET Smart Security, version of virus signature database 5113 (20100513) __________

    The message was checked by ESET Smart Security.

    http://www.eset.com
    Thursday, May 13, 2010 9:20 PM
    Moderator

All replies

  • Hello Raymon,

    I'm sorry but I have to tell you Project 2007 question is not supported in this  Project 2010 Customization and Programming forum, please check this thread for more details:
    http://social.msdn.microsoft.com/Forums/en-US/project2010custprog/thread/b19ba7db-a69b-4cf3-bc2b-64763f26cca6

    I suggest you post this question here: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.project which I've mentioned above.

    Thanks.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, May 13, 2010 9:44 AM
  • R Day:

    I expect that Rod or someone else with more VBA muscle will respond to your post. I'm going to respectfully disagree with Tim Li, that this is an inappropriate place to post your question as Microsoft's recent public announcements about the news groups closing specifically directed people to start using these forums. It couldn't hurt to double post though, as not all of our MVPs and other newsgroups experts have made the migration. And, if you are going to post this question to the newsgroups, the appropriate one would be http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.project.developer

    Not the one specified in Tim Li's post.


    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG

    Thursday, May 13, 2010 2:05 PM
    Moderator
  • Hi,
     
    Firstly it's better not have any tasks "living" in the master file.
     
    Your structure will be something like:
     
    Sub test()
    Dim Tsk As Task
    Dim subTsk As Task
    Dim subproj As Project
        For Each Tsk In ActiveProject.Tasks
            If Tsk.Subproject <> "" Then
                FileOpen Name:=Tsk.Subproject, ReadOnly:=True
                For Each subTsk In ActiveProject.Tasks
                    'Process data
                Next subTsk
                FileClose pjDoNotSave
            Else
                'Process data
            End If
        Next Tsk
    End Sub

    --

    Rod Gill
    Microsoft MVP for Project - http://www.project-systems.co.nz
     
    Author of the only book on Project VBA, see: http://www.projectvbabook.com
     

     
    "Raymondd7" wrote in message news:16d8f4c8-4e52-43cc-9f3c-912fa841e4cd...

    Gary-

    I am using Project 2007 Pro to extract time phased task usage data from a Master-SubProject structure on a Project 2007 Server and write the data to an Excel workbook.

    I found some code on Jach Dahlgren's site and was able to modify it to make it work on a standalone test project running locally. I then tried running the code on the local test project with a subproject inserted. I have done some additional reading and understand that the subproject tasks live within the projects in the subprojects collection.

    I have Rod Gill's book VBA Programming for Microsoft Office Project and have his code for looping through the subprojects. I have not yet been able to insert this code into my working code.  

    I am now trying to figure out how to loop through my test project, write the lines that are in the master project above the inserted sub project, then loop through the subproject and get its tasks, then loop through a subproject inserted into the first subproject. Once I am done here, I come back to the master project and pick up a task that lives within the master, a "project complete" milestone. This right here is my biggest stumbling block as I am struggling to figure out how to traverse the hierarchy.

    If my understanding is correct, I would need to loop while moving up and down between levels in the subproject hiearchy to extract each task. My goal is to, in Excel, mirror the task usage view, with the cost table applied, and columns of monthly timephased data that mirrors the timephased grid. Like I said, I can make it work in a stand alone project, but not in a consolidated project.

    Any direction would be most appreciated.

    R. Day

    EVM Project Manager - Serco North America 



    __________ Information from ESET Smart Security, version of virus signature database 5113 (20100513) __________

    The message was checked by ESET Smart Security.

    http://www.eset.com
    Thursday, May 13, 2010 9:20 PM
    Moderator
  • Private Sub TestSubprojects()
    '  Purpose: show that the Subproject object, used with the SourceProject method,
    '       can be used to identify a task within a multi-subproject project
      
      Dim prj As Subproject, _
        tsk As Task
      
      For Each prj In ActiveProject.Subprojects
        For Each tsk In prj.SourceProject.Tasks
          Debug.Print tsk.ID, tsk.Name
        Next tsk
      Next prj
    End Sub
    

    This is the solution I just stumbled upon.  It avoids having to open the files that contain each subproject, but still allows you to see the individual tasks within the subprojects.

     

    (For reasons I don't understand, the editor is showing the code block in proportional font, but after hitting "Submit" it shows up in the default font.)

    Marty

    Thursday, July 29, 2010 2:03 AM