Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Relationships in Project (2000/SR1)

    I do not understand the relationships of tasks, resources, and assignments in Project 2000. I think this is my problem in exporting custom fields and timephased data to Excel. With one macro, I have been able to export Resource Name and it lands in the right column in Excel. With a different macro, I can export a custom text field and it lands in the right column in Excel (although the other text fields don't export, with the same code). Basically, I want to export the Resource Name with timephased data by week, with a different row whenever each of three other custom text fields change. I also want to export the Resource Group to which the Resource Name belongs. I just have strong feeling that my lack of comprehension of field relationships in Project is creating my problems here, not the process of exporting to Excel, since I can get data to export.

    The segment of code that works for Resource Name is:

    'Write Resource fields to Excel
    Set xlR = xlR.Range("A2:A2")
    num = 0
    For Each R In ActiveProject.Resources
    num = num + 1
    xlR.Cells(num, 1).Value = T.ID 'does not work
    xlR.Cells(num, 2).Value = T.Text19 'does not work
    xlR.Cells(num, 3).Value = T.Text15 'does not work
    xlR.Cells(num, 4).Value = T.Text7 'does not work
    xlR.Cells(num, 5).Value = T.Group 'Resource Group does not work
    xlR.Cells(num, 6).Value = T.Text30 'does not work
    xlR.Cells(num, 7).Value = R.Name 'Resource Name works
    'End If
    Next R

    The code that works for TaskText30 is:

    'Write Task text fields to Excel - writes num but not text fields
    Set xlR = xlR.Range("A2:A2")
    num = 0
    For Each T In ActiveProject.Tasks
    For Each R In ActiveProject.Resources
    num = num + 1
    xlR.Cells(num, 1).Value = T.ID 'works
    xlR.Cells(num, 2).Value = T.Text19 ''does not work
    xlR.Cells(num, 3).Value = T.Text15 'does not work
    xlR.Cells(num, 4).Value = T.Text7 'does not work
    xlR.Cells(num, 5).Value = T.Group 'does not work
    xlR.Cells(num, 6).Value = T.Text30 'works
    'End If
    Next R
    Next T

    I've looked at everything that I can find in outside books, the MVP site, Google Groups, and wopr. I've looked at the object model but am still baffled.

    Can anyone help please, please?

    Thanks,
    Caroline

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Data Relationships in Project (2000/SR1)

    Caroline,

    At first glance, I noticed that you are using T.Group. Group is part of the Resource object, not Task.

    The following does not work as you say because you are using the resource object and trying to get task object data with it. You are creating a loop "For each R in ActiveProject.Resources" and tying to get T. (Task) object information. Notice that the only item that does work is the reference to the resource object, R.Name.

    'Write Resource fields to Excel
    Set xlR = xlR.Range("A2:A2")
    num = 0
    For Each R In ActiveProject.Resources
    num = num + 1
    xlR.Cells(num, 1).Value = T.ID 'does not work
    xlR.Cells(num, 2).Value = T.Text19 'does not work
    xlR.Cells(num, 3).Value = T.Text15 'does not work
    xlR.Cells(num, 4).Value = T.Text7 'does not work
    xlR.Cells(num, 5).Value = T.Group 'Resource Group does not work
    xlR.Cells(num, 6).Value = T.Text30 'does not work
    xlR.Cells(num, 7).Value = R.Name 'Resource Name works
    'End If
    Next R

    Now your second bit of code is a little more complex, closer to what you want, but wont work either.
    You create a Resource loop within a Task loop which is OK. But, you are mixing object types. T.Group should be R.Group. Try changing the code as shown below your code.

    YOUR CODE:
    'Write Task text fields to Excel - writes num but not text fields
    Set xlR = xlR.Range("A2:A2")
    num = 0
    For Each T In ActiveProject.Tasks
    For Each R In ActiveProject.Resources
    num = num + 1
    xlR.Cells(num, 1).Value = T.ID 'works
    xlR.Cells(num, 2).Value = T.Text19 ''does not work
    xlR.Cells(num, 3).Value = T.Text15 'does not work
    xlR.Cells(num, 4).Value = T.Text7 'does not work
    xlR.Cells(num, 5).Value = T.Group 'does not work
    xlR.Cells(num, 6).Value = T.Text30 'works
    'End If
    Next R
    Next T

    TRY THIS (or something like this - I dont have time to test it)

    'Write Task text fields to Excel - writes num but not text fields
    Set xlR = xlR.Range("A2:A2")
    num = 0
    For Each T In ActiveProject.Tasks
    num = num + 1
    xlR.Cells(num, 1).Value = T.ID 'works
    xlR.Cells(num, 2).Value = T.Text19 ''does not work
    xlR.Cells(num, 3).Value = T.Text15 'does not work
    xlR.Cells(num, 4).Value = T.Text7 'does not work
    xlR.Cells(num, 6).Value = T.Text30 'works

    For Each R In ActiveProject.Resources
    xlR.Cells(num, 5).Value = R.Group 'does not work
    Next R
    Next T

    What this will do is get your Task data and then loop through and get the resource data for each resource assigned to the task.

    Post back if you still cant get this to work.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Relationships in Project (2000/SR1)

    Gary, from your code, I get T.ID and T.Task30 in Excel. R.Group flashes for each row but doesn't stick in the cell. It also looks as though, for R.Group, several Resource Group names are being written to the same cell, one right after each other, before it goes to the next cell.

    Caroline

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Data Relationships in Project (2000/SR1)

    Caroline,

    Try putting a Num = Num+1 under "For Each R in ...." As Num is remaining constant, it keeps writing over the same cells.

    You probably need a TNum and a RNum; one counter for each loop. However, re-set the RNum counter to zero on each increment on the task. You could test without the second count incrementer and your data will just write horizontally.
    Hopefully, you understand what I mean.

    HTH
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Relationships in Project (2000/SR1)

    This worked. However, I'm still not getting any of the other text fields except T.ID. I'm starting to wonder if I should be working with assignment fields. The overall goal is to get timescaled data into Excel. I want Resource Group with peak units by week. The other data (T.Text19, T.Text15, T.Text7) provides additional information about the Resource Group. I need to select only the rows in the project plan that have T.Text19, T.Text15, T.Text7, Resource Group, and T.Text30; those are the only ones that have peak usage data associated with them in the view of timescaled data.

    Is this as clear as mud?

    Thanks,
    Caroline

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Data Relationships in Project (2000/SR1)

    Caroline,

    Clear as mud is fine. One item you may be running up against is that there are text fields associated with both the Task and Resource data. T.Text1 is not the same as R.Text1.

    If you have the Pyron book, check out the CD in the back which provides an overview of the MS Project Table and Field structure which should help. You might also try the Microsoft Knowledgebase for exporting resource data.

    Your statement to assignment field jogged my memory and you are correct. You need to extract the assignments by resource to get the timephased data. Also note that project writes the timephased data in unique ways. I like to refer to them as "chunks" but will see if you can figure it out. Don't be surprised if the timephased data is not spread as you expect. Isn't this fun.

    Post back with more questions. I will try to keep checking over the weekend. Sorry I have not had more time to assist you. Us old guys keep forgetting everything....
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Relationships in Project (2000/SR1)

    I've looked at the Pyron CD but still am not much closer. I finally got the Analyze Timescaled Data macro to load and run, and it is fairly close to the timescale side of the data that I want to capture. Do you or anyone out there have the macro code to Analyze_Timescale_Data that came with Project 98? I haven't been able to find it on the Microsoft site, and I think that it would really help me as a base.

    Thanks,
    Caroline

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Data Relationships in Project (2000/SR1)

    Caroline,

    Glad to hear you got the macro to work. Any chance you could post it to benefit others? I can't say I have the data for Project 98. As to the table names and structures, I will see if I can dig up the file and send it to you.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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