none
Looking for help with VBA to dump Resource Usage with Tasks RRS feed

  • Question

  • <p>I have somve visual Basic (below) that pulls the resource allocation by resource by week. It works great. I want to add a layer of detail and break it out by task as well, the say way the Resource Usage view shows it.</p><p>Is there a way I can dump that view, with both Resource and Task, or is there a way to add the task detail to my code?</p><p>thanks</p><p>Not the code bolow is actually in Visual Basic, but in the "Insert code Block" option, I did not see that so chose VB.net.</p><pre class="prettyprint lang-vb">Sub TimePhasedDataTest_save() Dim tsv_task As TimeScaleValue Dim tsvs_task As TimeScaleValues Dim tsv_res As TimeScaleValue Dim tsvs_res As TimeScaleValues Dim s_Task_Name As Variant ' Declare a FileSystemObject. Needs Microsoft Scripting Runtime Reference Dim fso As FileSystemObject ' Create a FileSystemObject. Set fso = New FileSystemObject ' Declare a TextStream. Dim stream As TextStream ' Create a TextStream. Set stream = fso.CreateTextFile("C:\Users\trich\Desktop\TimeScaleData Work.csv", True) Dim l_Number_Of_Resources As Long Dim l_Number_of_Tasks As Long Dim i As Long Dim j As Long Dim s_String As String 'Timephased for Resource l_Number_Of_Resources = ActiveProject.ResourceCount l_Number_of_Tasks = ActiveProject.Tasks.Count 'Write out the first row of dates so set task to 1, the main summary task Set tsvs_task = ActiveProject.Tasks(1).TimeScaleData( _ StartDate:=ActiveProject.ProjectStart, _ EndDate:=ActiveProject.ProjectFinish, _ Type:=pjTaskTimescaledWork, _ TimeScaleUnit:=pjTimescaleWeeks, Count:=1) stream.Write "," ' this is for the file name column For Each tsv_task In tsvs_task stream.Write "," &amp; Format(tsv_task.StartDate, "Short Date") Next tsv_task 'now write a blank line to get ready for the resour data below stream.WriteLine "," For j = 1 To l_Number_Of_Resources Set tsvs_res = ActiveProject.Resources(j).TimeScaleData( _ StartDate:=ActiveProject.ProjectStart, _ EndDate:=ActiveProject.ProjectFinish, _ Type:=pjResourceTimescaledWork, _ TimeScaleUnit:=pjTimescaleWeeks, Count:=1) i = 1 For Each tsv_res In tsvs_res 'Debug.Print ActiveProject.Resources(j).Name, _ Format(tsv.StartDate, "Short Date"), _ Val(tsv.Value) / 60; "" If i = 1 Then 'we have a new resource and need a new line stream.WriteLine s_String = ActiveProject.Name &amp; "," &amp; _ ActiveProject.Resources(j).Name &amp; "," &amp; _ Val(tsv_res.Value) / 60 &amp; "," stream.Write s_String i = i + 1 Else ' we are continuing with the current resource s_String = Val(tsv_res.Value) / 60 &amp; "," stream.Write s_String End If Next tsv_res Next j stream.Close MsgBox "Processing Complete." End Sub</pre><br /><p></p>
    Wednesday, March 27, 2013 2:53 PM

Answers

  • Hi,µ

    Just without testing, this seems wrong:

    Type:=pjTaskTimescaledWork, _

    should read

    Type:=pjassignmentTimescaledWork, _

    You are NOT reading task data but assignlent data!

    Greetings,

    Wednesday, March 27, 2013 8:51 PM
    Moderator
  • Success

    For completeness I write a header like this:

        stream_tall.WriteLine "Project, Task, Group, Resource, Date, Hours"

    The write each item like this:

        For c_res = 1 To l_Number_Of_Resources
            For Each asg In ActiveProject.Resources(c_res).Assignments
                Set tsvs = asg.TimeScaleData( _
                    StartDate:=ActiveProject.ProjectStart, _
                    EndDate:=ActiveProject.ProjectFinish, _
                    Type:=pjAssignmentTimescaledWork, _
                    TimeScaleUnit:=pjTimescaleWeeks, Count:=1)
                For Each tsv In tsvs
                    'MsgBox Val(tsv.Value) / 60 & ","
                    s_String = ActiveProject.Name & ","
                    s_String = s_String & asg.TaskName & ","
                    s_String = s_String & ActiveProject.Resources(c_res).Group & ","
                    s_String = s_String & ActiveProject.Resources(c_res).Name & ","
                    s_String = s_String & Format(tsv.StartDate, "Short Date") & ","
                    s_String = s_String & Val(tsv.Value) / 60 & ","
                    stream_tall.WriteLine s_String
                Next tsv
            Next asg
        Next c_res

    thanks!

    • Marked as answer by FlyBoy54 Wednesday, March 27, 2013 9:49 PM
    Wednesday, March 27, 2013 9:49 PM

All replies

  • Hello Fly Boy,

    Can you try pasting that again? It's a bit hard to read without the carriage returns, but I'm sure someone will be able to help with this.

    Don't worry about the codeblock option being VB.net, that won't be an issue, but the carriage returns are important.

    Ta,

    Andrew

    Wednesday, March 27, 2013 3:40 PM
  • Hi,

    Since you seem very knowledgeable on Timescalevalues, maybe it's enough that I point you to "assignment timescale values" which are by resource by task. So when you have a resource R you could code

    For each asg in R.assignments

    set tsv=asg.timescaledata(..)

    ...

    Next asg

    Greetings,

    Wednesday, March 27, 2013 7:29 PM
    Moderator
  • Jan,

    Here is what I coded up. (Sorry but for some reason I cannot get the code block to work.)

    Iget an error (the Argument value is not valid) when I try to set tsvs. but all of the paravaters have values.

       Dim asg As Assignment
       Dim tsv As TimeScaleValue
       Dim tsvs As TimeScaleValues

       Dim l_Number_Of_Resources As Long
       Dim c_res As Long

        For c_res = 1 To l_Number_Of_Resources
            For Each asg In ActiveProject.Resources(c_res).Assignments
                Set tsvs = asg.TimeScaleData( _
                    StartDate:=ActiveProject.ProjectStart, _
                    EndDate:=ActiveProject.ProjectFinish, _
                    Type:=pjTaskTimescaledWork, _
                    TimeScaleUnit:=pjTimescaleWeeks, Count:=1)
                For Each tsv In tsvs
                    MsgBox Val(tsv.Value) / 60 & ","
                Next tsv
            Next asg
        Next c_res

    Wednesday, March 27, 2013 8:38 PM
  • Earlier I had this also:

       l_Number_Of_Resources = ActiveProject.ResourceCount

    Wednesday, March 27, 2013 8:39 PM
  • Hi,µ

    Just without testing, this seems wrong:

    Type:=pjTaskTimescaledWork, _

    should read

    Type:=pjassignmentTimescaledWork, _

    You are NOT reading task data but assignlent data!

    Greetings,

    Wednesday, March 27, 2013 8:51 PM
    Moderator
  • Hi,

    ActiveProject.Resources.Count

    Greetings,

    • Marked as answer by FlyBoy54 Wednesday, March 27, 2013 9:17 PM
    • Unmarked as answer by FlyBoy54 Wednesday, March 27, 2013 9:47 PM
    Wednesday, March 27, 2013 8:52 PM
    Moderator
  • Success

    For completeness I write a header like this:

        stream_tall.WriteLine "Project, Task, Group, Resource, Date, Hours"

    The write each item like this:

        For c_res = 1 To l_Number_Of_Resources
            For Each asg In ActiveProject.Resources(c_res).Assignments
                Set tsvs = asg.TimeScaleData( _
                    StartDate:=ActiveProject.ProjectStart, _
                    EndDate:=ActiveProject.ProjectFinish, _
                    Type:=pjAssignmentTimescaledWork, _
                    TimeScaleUnit:=pjTimescaleWeeks, Count:=1)
                For Each tsv In tsvs
                    'MsgBox Val(tsv.Value) / 60 & ","
                    s_String = ActiveProject.Name & ","
                    s_String = s_String & asg.TaskName & ","
                    s_String = s_String & ActiveProject.Resources(c_res).Group & ","
                    s_String = s_String & ActiveProject.Resources(c_res).Name & ","
                    s_String = s_String & Format(tsv.StartDate, "Short Date") & ","
                    s_String = s_String & Val(tsv.Value) / 60 & ","
                    stream_tall.WriteLine s_String
                Next tsv
            Next asg
        Next c_res

    thanks!

    • Marked as answer by FlyBoy54 Wednesday, March 27, 2013 9:49 PM
    Wednesday, March 27, 2013 9:49 PM