none
Help with Modifying Existing code RRS feed

  • Question

  • Hi All

    I have been sucessfully using a piece of code kindly provided by Jack Dahlgren some years ago.  However I have a need to slightly modify it.  I only want to add the previous Outline Level value (not all of the previous ones).  I am struggling with this one and need spome help.  Here is the code:

    Sub summaryname()
    'This Macro will create the entire "Path" to a specific task by adding together the
    'names of all the parent tasks. It is useful when 'you have many commonly named substasks
    'and want to know where in the heirarchy they reside. As written, this places the "path" in the
    'Text12 custom field. You could alternately put this into the task name field itself, however
    'that may make the names rather long
    'Copyright Jack Dahlgren, March 2002

    Dim mystring As String
    Dim mytask As Task
    Dim myoutlinelevel As Integer
    myoutlinelevel = 1
    While myoutlinelevel < 10
    For Each mytask In ActiveProject.Tasks
        If Not (mytask Is Nothing) Then
            If mytask.Text12 = "" Then
                If mytask.OutlineLevel = myoutlinelevel Then
                    mytask.Text12 = mytask.OutlineParent.Text12 & " | " & mytask.Name
                End If
            End If
        End If
    Next mytask
    myoutlinelevel = myoutlinelevel + 1
    Wend
    MsgBox ("Customised Task List Names have been built for each task " & vbCrLf & "and are contained in Field Text12." & vbCrLf & vbCrLf & "This text field is used to build individual Task Reports for " & vbCrLf & "workstream resources." & vbCrLf & vbCrLf & "PLEASE DO NOT MODIFY.")
    End Sub

    Many thanks in anticipation.

    Tony


    TKHussar

    Tuesday, May 20, 2014 3:19 PM

Answers

  • Hello,

    Your post confirms the suspicion I mentioned in my previous post:

    As for your problem, you probably copied the value of task.text5 into the "task-defined" assignment value, whereas what you copy to Excel is the resource-defined text5 value

    As indicated in the second part of the faq text, you will have to copy the value from the task-defined assignment.text5 into the resource-defined assignment.text5. The VBA procedure of how to make ths copy is in the FAQ (I'm supposed to know, I wrote it).

    Good luck,

    Thursday, May 22, 2014 11:47 AM
    Moderator

All replies

  • Hi Tony,

    Thank you for posting in the MSDN Forum.

    Since the issue is more relate to the project programming, I'd like to move it to Project Customization and Programming forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 21, 2014 7:21 AM
  • Please..

    Dim mytask As Task
    For Each mytask In ActiveProject.Tasks
        If Not (mytask Is Nothing) Then
                       If mytask.OutlineLevel >1 Then
                    mytask.Text12 = mytask.OutlineParent.name & " | " & mytask.Name
                End If
               End If
    Next mytask
    MsgBox ("Customised Task List Names have been built for each task " & vbCrLf & "and are contained in Field Text12." & vbCrLf & vbCrLf & "This text field is used to build individual Task Reports for " & vbCrLf & "workstream resources." & vbCrLf & vbCrLf & "PLEASE DO NOT MODIFY.")
    End Sub

    Wednesday, May 21, 2014 8:04 AM
    Moderator
  • Hi Jan

    That was perfect.  Many thanks.

    The only other issue I have now is that I have a piece of code that copies the value in mytask.Text12 to an assignment field called a.Text5 within my code.  However when I try and export the value to Excel the field is blank.  Here is a snippet of my code:

    rName = Ass.ResourceName
    s.Range("A" & Row).Value = Ass.ResourceName
    s.Range("B" & Row).Value = Ass.TaskUniqueID
    s.Range("C" & Row).Value = Ass.Text5
    s.Range("D" & Row).Value = Ass.TaskName
    s.Range("E" & Row).Value = Ass.Start
    s.Range("G" & Row).Value = Ass.Finish

    Any help/advice would be appreciated.

    Thanks

    Tony


    TKHussar

    Wednesday, May 21, 2014 10:17 AM
  • Hi,

    Be aware that for any assignment, custom fields like Text5 have two values. Incredible, but true. If an assignment is defined as depending of a task, custom fields are not the same than when it is defined as depending from a resource, fo instance:

    activeproject.tasks(5).assignments(3).Text1

    may be different from

    activeproject.resources(7).assignments(2).text1

    Even when the two definitions refer to the same assignment!

    As for your problem, you probably copied the value of task.text5 into the "task-defined" assignment value, whereas what you copy to Excel is the resource-defined text5 value

    Do look up: http://project.mvps.org/faqs.htm

    Faq 37, Custom fields in Tables, ahs the code to copy one valu of a custom field for an assignment into the othe one.

    Greetings,


    Wednesday, May 21, 2014 11:22 AM
    Moderator
  • Hi Jan

    Thanks for your prompt response.  The code from FAQ37 is what is used initially.  However when I try and produce individual task lists nothing is output from Ass.Text5.  My code is below:

    For Each Res In ActiveProject.Resources
        If Res.Assignments.Count > 0 Then
            Row = 5
            XlApp.Workbooks.Open ("D:\Task List Templates\Task List Template.xlsm")
            BookNam = XlApp.ActiveWorkbook.Name
            Set s = XlApp.Workbooks(BookNam).Worksheets(1)
            For Each Ass In Res.Assignments
                Set xlRange = s.Range("A5")
                If Ass.PercentWorkComplete < 100 Then
                        With xlRange
                            rName = Ass.ResourceName
                            s.Range("A" & Row).Value = Ass.ResourceName
                            s.Range("B" & Row).Value = Ass.TaskUniqueID
                            s.Range("C" & Row).Value = Ass.Text5
                            s.Range("D" & Row).Value = Ass.TaskName
                            s.Range("E" & Row).Value = Ass.Start
                            s.Range("G" & Row).Value = Ass.Finish
                        End With
                End If
                Row = Row + 1
                Set xlRange = xlRange.Offset(Row, 0)  'Point to next row
            Next
            XlApp.Visible = True
            Application.DisplayAlerts = False
            If rName = "" Then
                GoTo NextOne
            End If
            XlApp.ActiveWorkbook.SaveAs FileName:= _
                "D:\Task List Templates\Task Lists\" & rName & ".xlsm", FileFormat:= _
                xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
            rName = ""
            XlApp.ActiveWorkbook.Close savechanges:=False
            Application.DisplayAlerts = True
        End If
    NextOne:
    Next

    However when I export to excel everything else I need gets exported perfectly but not the a.Text5 values.  When I check the Task Usage view and add the Text5 column the value is there but only against the Resource name and not the task name.  Hope that makes sense.

    Thanks

    Tony


    TKHussar


    • Edited by TKHussar Wednesday, May 21, 2014 1:33 PM
    Wednesday, May 21, 2014 11:51 AM
  • Hello,

    Your post confirms the suspicion I mentioned in my previous post:

    As for your problem, you probably copied the value of task.text5 into the "task-defined" assignment value, whereas what you copy to Excel is the resource-defined text5 value

    As indicated in the second part of the faq text, you will have to copy the value from the task-defined assignment.text5 into the resource-defined assignment.text5. The VBA procedure of how to make ths copy is in the FAQ (I'm supposed to know, I wrote it).

    Good luck,

    Thursday, May 22, 2014 11:47 AM
    Moderator
  • Hi Jan

    Thanks for your response.  I have now resolved the issue (thanks to you).  Sometime you are to close to see the "wood for the trees" as wew say in the UK.

    Thanks again.

    Tony


    TKHussar

    Thursday, May 22, 2014 1:40 PM