none
Collecting and Storing Successor Task Descriptions using VBA

    Pertanyaan

  • Hi All

    I am looking for some help in modifying my code to store the description of each task successor on a separate row in a variable in MS Project.  The code I have so far is shown below for information.  Any help appreciated as always:

    Sub CheckSuccessors()
    Dim ts As Tasks                          'Active task selection
    Dim t As Task
    Dim tSucc As Task
    Dim succCnt As Integer
    For Each t In ActiveProject.Tasks
        For Each tSucc In t.SuccessorTasks
            succCnt = t.SuccessorTasks.Count
            'Debug.Print succCnt
            For Count = 1 To succCnt
                If tskSucc = "" Then
                    tskSucc = tSucc.Name
                    Else
                    tskSucc = tskSucc & vcCrLf & tSucc.Name
                End If
            Next
            Debug.Print "ID: " & t.ID & " " _
                        & "No Of Succs: " & t.SuccessorTasks.Count & " " _
                        & "Unique ID: " & tSucc & " " _
                        & "Succ Task Desc: " & tSucc.Name & " " _
                        & "Dep Name(s): " & tskSucc
            tskSucc = ""
        Next tSucc
    Next t
    End Sub
    Tony


    TKHussar

    07 Juni 2018 12:55

Semua Balasan

  • Hello Tony,

    This forum(General Office Development) usually discuss development issue related to Word, Excel, Outlook, PowerPoint.. And your issue is more related to MS Project and I think Project Customization and Programming forum is more suitable for your issue. So I would move the thread to the forum.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    08 Juni 2018 6:19
  • Thanks Terry.  I will move it.

    Tony


    TKHussar

    08 Juni 2018 9:44
  • Hi All

    I am looking for some help in modifying my code to store the description of each task successor on a separate row in a variable in MS Project.  The code I have so far is shown below for information.  Any help appreciated as always:

    Sub CheckSuccessors()
    Dim ts As Tasks                          'Active task selection
    Dim t As Task
    Dim tSucc As Task
    Dim succCnt As Integer
    For Each t In ActiveProject.Tasks
        For Each tSucc In t.SuccessorTasks
            succCnt = t.SuccessorTasks.Count
            'Debug.Print succCnt
            For Count = 1 To succCnt
                If tskSucc = "" Then
                    tskSucc = tSucc.Name
                    Else
                    tskSucc = tskSucc & vcCrLf & tSucc.Name
                End If
            Next
            Debug.Print "ID: " & t.ID & " " _
                        & "No Of Succs: " & t.SuccessorTasks.Count & " " _
                        & "Unique ID: " & tSucc & " " _
                        & "Succ Task Desc: " & tSucc.Name & " " _
                        & "Dep Name(s): " & tskSucc
            tskSucc = ""
        Next tSucc
    Next t
    End Sub

    Kind regards

    Tony


    TKHussar

    08 Juni 2018 9:46
  • Tony,

    Your code is kind of an un-runnable mishmash, ending with simple prints to the immediate window in the VBA editor.  The result you want is pretty simple to get, but you need to clarify how you want the output.  I.e. what do you mean by "...store the description of each task successor on a separate row in a variable in MS Project?"

    08 Juni 2018 15:08
  • Tony,

    The description of what you want to do is very confusing. What exactly are you trying to store and where?

    However, does this do what you want?

    Option Explicit
    Sub CheckSuccessors()
    Dim t As Task
    Dim tSucc As Task
    Dim succCnt As Integer
    Dim tskSucc As String
    For Each t In ActiveProject.Tasks
        For Each tSucc In t.SuccessorTasks
            succCnt = t.SuccessorTasks.Count
            If tskSucc = "" Then
                tskSucc = tSucc.Name
            Else
                tskSucc = tskSucc & "," & tSucc.Name
            End If
            Debug.Print "ID: " & t.ID & " " _
                & "No Of Succs: " & t.SuccessorTasks.Count & " " _
                & "Unique ID: " & tSucc.UniqueID & " " _
                & "Succ Task Desc: " & tSucc.Name & " " _
                & "Dep Name(s): " & tskSucc
            tskSucc = ""
        Next tSucc
    Next t
    End Sub

    John

    08 Juni 2018 15:10
  • Hi Tom/John

    Sorry for the confusion.  I have a piece of code that produces individual Task Lists in Excel for all activities in a plan.  I have now been asked to add the descriptions of all Successors to each task row.  So I need to store the "Name" of each Successor in a variable (one Name per row) so I can paste into the Excel Task List.   For example:

    Main Task

    Stand up Training environment

    Successor Tasks

    Populate Training Environment with Training Data

    Review Training Environment Data

    So the two successor tasks descriptions need to be stored on a separate line in a variable so I can use that variable to paste into the Excel Task list against the main task.

    Hope this makes sense.

    Thanks

    Tony


    TKHussar

    11 Juni 2018 10:24
  • Hi Tony,

    Actually it still doesn't make a whole lot of sense in the context of MS Project - where "stored on a separate line in a variable" is essentially without meaning. 

    Project has task objects, each of which can be made to occupy a "line" or "row" in a task table.  You can't create new "lines" in the task table for anything other than tasks.  Each task has up to 30 custom text fields available, and you could copy the successor names into these fields for later copy/pasting into Excel.  IMO that's not a very efficient use of these fields, which tend to become a scarce resource during project execution.

    John's technique concatenates the successor names into a single CSV string for each task.  That string could be delivered via VBA to Excel, where it could be parsed into its separate values.

    Essentially, getting the successor names for each task t is simple: just loop through t.SuccessorTasks (from i= 1 to t.SuccessorTasks.Count) and read t.SuccessorTasks(i).Name.  The question is, what do you want to do with that name in Project? In Excel?  Since the ultimate target seems to be an Excel workbook, the simplest thing seems to be to read and pass each value directly to the target cell in Excel.  For a larger project, it may be faster to read all the data (tasks and successors) to an intermediate array variable, which is then passed to Excel via VBA. In either case the details depend on your Excel data structure.   

       

    11 Juni 2018 15:13
  • Hi Tom

    Many thanks for your feedback.  I will take that on board and see how I get on.

    Kind regards

    Tony


    TKHussar

    11 Juni 2018 16:33
  • Hi John

    Many thanks for the code.  I will give it a go and get back to you if I have any queries.

    Kind regards

    Tony


    TKHussar

    11 Juni 2018 16:34
  • Tony,

    I think Tom summarized what I was also thinking. Why bother with a paste, just export the Project data directly to Excel where you can format it in any way you need/want.

    John

    11 Juni 2018 17:02
  • Hi Tom

    Apologies if I have misled you (or anyone else looking at this post).

    With help from John a couple of years ago I developed a script that exports all activity assignments to Excel by Resource.  So each resource is provided with a 28 day lookahead of activities assigned to them.  This works perfectly.  However I have now been asked to look at adding the tasks names of any Successors for each task assigned to each resource and include that in each individual task list against each task assignment.  SO whilst (with your help) I can now collate all Successor task names into a single variable I am struggling at how to export the value of that variable into Excel with all assignment data.

    I attach a copy of the code that John helped me develop below for information.

    Thanks in advance for any help/guidance.  Hope I have explained my challenge clearly:

    Sub PrintResourceCharts()
    Dim xlApp As Object 'Excel.Application
    Dim xlRange As Excel.Range
    Dim rName As String
    Dim tsk As Task
    Dim res As Resource
    Dim ass As Assignment
    Dim s As Worksheet
    Dim Booknam As String
    Dim Row As Integer
    Dim FName As String
    Dim tSucc As Task
     
    Call summaryname
    Call Task_CF_To_Resource_Usage
     
    'Remove Existing Task List files from directory before creating new ones
    On Error GoTo Finish
    Kill "H:\Task List Templates\Task Lists\*.xlsx"
    Finish:
     
    'Save File Location
    FName = "H:\Task List Templates\Task Lists\"
     
    'Start Excel and Create a new Workbook
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
     
    'Export Resource and Task details
    Application.DisplayAlerts = False
    For Each res In ActiveProject.Resources
        If res.Assignments.count > 0 Then
            Row = 5
            xlApp.Workbooks.Open ("H:\Task List Templates\Task List Template.xlsx")
            Booknam = xlApp.ActiveWorkbook.Name
            'Set s = xlApp.Workbooks(BookNam).Worksheets(1)
            For Each ass In res.Assignments
                Set xlRange = xlApp.Range("A5")
                'If Not ass.ActualWork = "0" Then
                If ass.PercentWorkComplete < 100 Then
                    If ass.Finish < Now() + 28 Then
                        With xlRange
                            rName = ass.ResourceName
                            xlApp.Range("A" & Row).Value = ass.ResourceName
                            xlApp.Range("B" & Row).Value = ass.TaskUniqueID
                            xlApp.Range("D" & Row).Value = ass.Text13
                            xlApp.Range("E" & Row).Value = ass.BaselineStart
                            xlApp.Range("F" & Row).Value = ass.Start
                            xlApp.Range("H" & Row).Value = ass.Finish
                            xlApp.Range("L" & Row).Value = ass.Text14
                        End With
                    End If
                End If
                '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
            
            'Export Email Address to Task List
            Set xlRange = xlApp.Range("A3")
            With xlRange
                rAddress = res.EMailAddress
                xlApp.Range("A3").Value = rAddress
            End With
            'Sort Tasks by Start Date
            Set objRange = xlApp.ActiveWorkbook.Worksheets("Task_List").Range("A5:L1000")
            objRange.Sort Key1:=objRange.Parent.Range("E4")  ', Key2:=objRange.Parent.Range("G3")
            
    '    ActiveWorkbook.SaveAs FileName:= _
    '        "H:\Task List Templates\Task Lists\Alasdair Shutt.xlsx", FileFormat:= _
    '        xlOpenXMLWorkbook, CreateBackup:=False
                
            xlApp.ActiveWorkbook.SaveAs FileName:= _
                "H:\Task List Templates\Task Lists\" & rName & ".xlsx", FileFormat:= _
                xlOpenXMLWorkbook, CreateBackup:=False
     
    'Send Task List to named Resource
            Dim ToMsg As String
            Dim Recipient As String
            Dim objOutlook As Object
            If rAddress = "" Then
                GoTo NextOne
            End If
            Set objOutlook = CreateObject("Outlook.Application")
     
            Dim ObjEmail As Object
            Set ObjEmail = objOutlook.CreateItem(olMailItem)
     
            'VBA Create email
            ToMsg = "Please see attached Task List document which lists the activities assigned to you for the CRM Programme." & vbCrLf _
                & vbCrLf & "You are requested to update progress in the Task List document and send it back to me by close of play each Friday." & vbCrLf _
                & vbCrLf & "The plan will be updated on the following Mony morning and a fresh Task List will be sent out directly afterwards." & vbCrLf _
                & vbCrLf & "Please only respond with an update for those activities that are either due to start or finish this week." _
                & vbCrLf & "If you feel that activities have been assigned to you incorrectly then please contact Barry Rimmington to discuss and resolve." _
                & vbCrLf & vbCrLf & "PLEASE ENSURE THAT YOUR UPDATES ARE PROVIDED BY COP EACH FRIDAY AS WE NEED TO PROVIDE ACCURATE REPORTING BY MIDDAY EACH MONDAY TO THE STEERING GROUP." _
                & vbCrLf & vbCrLf & "Tony" & vbCrLf & "CRM Programme Planner"
     
            With ObjEmail
                .To = rAddress
                .CC = ""
                .BCC = ""
                .Subject = "CRM Task Activities Assigned To You Covering the next 28 days"
                .Body = ToMsg
                .Attachments.Add "H:\Task List Templates\Task Lists\" & rName & ".xlsx"
                .Send 'Display 'to send automatic mail from excel instead of .display use .send
            End With
            Set ObjEmail = Nothing: Set objOutlook = Nothing
     
            xlApp.ActiveWorkbook.Close SaveChanges:=False
            Application.DisplayAlerts = True
        End If
    rName = ""
    NextOne:
    Next
    Application.DisplayAlerts = True
    xlApp.Application.Quit
    Set xlApp = Nothing
    MsgBox ("Individual Task Lists have now been produced....")
    End Sub

    Thanks in advance

    Tony


    TKHussar

    15 Juni 2018 9:01
  • Tony,

    Now that you've clarified your intentions, I'll defer to John - this is right in his wheelhouse.

    After a few minutes thought, it seems something like this would work:

    1. In the "With xlRange" block, append a line for column M - Successors.

    2. Include a new "Successors" function in the module. 

    This would list all the successor tasks by name (on separate lines) in a single cell.

    xlApp.Range("M" & Row).Value = Successors(ass)

    Function Successors(ByRef a As Assignment) As String Dim i As Integer For i = 1 To a.Task.SuccessorTasks.Count If i = a.Task.SuccessorTasks.Count Then Successors = Successors & a.Task.SuccessorTasks(i).Name Else Successors = Successors & a.Task.SuccessorTasks(i).Name & _

    "," & vbCrLf End If Next i End Function


    Good luck, tom

    15 Juni 2018 13:21
  • Tony,

    Rather than me spending more time with an alternate approach why not try Tom's suggestion. If that doesn't work, let us know and we'll help further.

    John

    15 Juni 2018 16:05