none
VBA to identify a group of tasks being updated RRS feed

  • Question

  • Good Afternoon,
    I have not had much luck with Project questions here, but now that I have dug a little deeper I think I can ask a question that you may be able to help with. If any of you know of a better place to find Project VBA advice, please let me know! 

    This question is also gathering dust over at UA. http://www.utteraccess.com/forum/Custom-Macros-Project-20-t2022616.html

    First off, I am writing a macro that is triggered when a task is updated in project. I used the code from this page as a starting point.
    http://msdn.microsoft.com/en-us/library/ee...fice.12%29.aspx

    The macro I wrote works well. I changed the code up a bit so that it runs each time the user sets a task to 100% complete. When the user sets a task to 100% complete, the macro pulls today's date and adds it to the custom date field (Date2).

    My next challenge is to warn the user if they update start or finish dates for a task with a specific tag in a text field (Text15). This new functionality will be embedded with the previous functionality I already wrote. Here is the problem. When I need to update a bunch of tasks, or when I update one task that changes other tasks due to links, the event is triggered for each and every task. This creates lots of message boxes that I don't need.

    On the surface this makes sense because I want to know each time a task changes, but from a practicality standpoint I only need to be notified once for each group of tasks that are updated. For example, if I update one date, and linking causes 10 other dates to change, I only want to be notified once. Similarly, if I highlight and change multiple dates I only want to get warned about it once, not once for each task.

    To achieve this I think I need to access some kind of collection that represents the set of tasks that are being updated as a result of user input. This is where I need help. I am not sure how to access this information.


    For your information here are the code elements I have written for the existing macro.

    This section goes in "ThisProject":

    <code>
    Private Sub Project_Open(ByVal pj As Project)
       EnableEvents
    End Sub

    </code>

    This section goes in a module named "modTaskUpdate":

    <code>
    Public TskUpdate As New clsTaskUpdate

    Sub EnableEvents()
       Set TskUpdate.App = MSProject.Application
    End Sub

    </code>

    Finally, this section goes in a Class Module named "clsTaskUpdate":

    <code>

    Public WithEvents App As MSProject.Application

    Private Sub App_ProjectBeforeTaskChange(ByVal tsk As Task, ByVal Field As Long, ByVal NewVal As Variant, Cancel As Boolean)

    'Code for managing 100% complete and Date Completed

    '(This part works OK, see the next section for the code I need to modify)
        If ((Field = pjTaskPercentComplete) And (NewVal = 100)) Then
            'Add today's date to completed date field
            'MsgBox "Adding today's date: " & Date & " to the Date2(DateCompleted) field."
            tsk.Date2 = Date
        End If
        If ((Field = pjTaskPercentComplete) And (NewVal = 0)) Then
            'Remove date from completed date field
            'MsgBox "Removing date from the Date2(DateCompleted) field."
            tsk.Date2 = ""
        End If

    'Code for managing warnings to user if PST dates will change.
    ' Currently this message is triggered for each task that causes a change.
    ' Can we only trigger this task once for each group of changed dates?

        If ((Field = pjTaskStart) Or (Field = pjTaskFinish)) Then
            If (insrt(tsk.Text15, "PST") <> 0) Then
                MsgBox "Warning! You have changed a date that is linked to a line in the PST. As a result the PST has been automatically updated."
            End If
        End If
    End Sub

    </code>

    FYI: The PST is an external Excel file that has its values automatically update via linked cells in Project.

    Thank you for your time and consideration.
    Please let me know if you have any questions.
    Nate

    Tuesday, October 7, 2014 2:01 PM

Answers

  • Nate,

    Yes, as far as I know, there is no "collection" of task changes. Remember, a collection is a group of objects and a change isn't an object.

    I see you found one of the posts I responded to regarding paste links. If you have ABSOLUTE file discipline management, then you might be okay with paste links, but since there is a much better alternative (i.e. VBA) to do what you need I'd avoid the paste link idea. As Clint Eastwood said in his Dirty Harry movie, "Do you feel lucky?"

    There have been various posts with code to export Project data to Excel. Unfortunately I find the search capabilities of these forums to be sorely lacking so rather than a link, here is some sample code you can adapt.

    Option Explicit
    Sub CalendarExceptions()
    'Basic macro code created by Kiran.K and posted on MSDN Project
    ' customizing and programming forum Feb 7,2013
    'Code streamlined and updated by John - Project June 2,2014

    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    Dim i As Integer, j As Integer
    Dim E As Exception
    Dim r As Resource
    Dim xlRng As Range

    'open Excel, define workbook, and set column headers
    MyXL.Workbooks.Add
    MyXL.Visible = True
    MyXL.ActiveWorkbook.Worksheets.Add.Name = "Exception Report"
    MyXL.ActiveWorkbook.Worksheets("Exception Report").Activate
    Set xlRng = MyXL.ActiveSheet.Range("A1")
    xlRng.Range("A1") = "Proj Cal Holidays"
    xlRng.Range("B1") = "Start Date"
    xlRng.Range("C1") = "Finish Date"
    xlRng.Range("E1") = "Res Name"
    xlRng.Range("F1") = "Res Base Cal"
    xlRng.Range("G1") = "Base Cal Excep"
    xlRng.Range("H1") = "Start Date"
    xlRng.Range("I1") = "Finish Date"
    xlRng.Range("K1") = "Resource Name"
    xlRng.Range("L1") = "Res Excep"
    xlRng.Range("M1") = "Start Date"
    xlRng.Range("N1") = "Finish Date"

    'First gather and export Project calendar exceptions
    i = 2
    If ActiveProject.Calendar.Exceptions.Count > 0 Then
        For Each E In ActiveProject.Calendar.Exceptions
            xlRng.Range("A" & i) = E.Name
            xlRng.Range("B" & i) = E.Start
            xlRng.Range("C" & i) = E.Finish
            i = i + 1
        Next
    End If

    'Next, gather and export resource base calendar exceptions along with
    '   resource calendar exceptions
    i = 2
    For Each r In ActiveProject.Resources
        If Not r Is Nothing Then
            j = i
            If r.Type = pjResourceTypeWork Then
                    For Each E In r.Calendar.BaseCalendar.Exceptions
                        xlRng.Range("E" & i) = r.Name
                        xlRng.Range("F" & i) = r.Calendar.BaseCalendar.Name
                        xlRng.Range("G" & i) = E.Name
                        xlRng.Range("H" & i) = E.Start
                        xlRng.Range("I" & i) = E.Finish
                        i = i + 1
                    Next E
                    For Each E In r.Calendar.Exceptions
                        xlRng.Range("K" & j) = r.Name
                        xlRng.Range("L" & j) = E.Name
                        xlRng.Range("M" & j) = E.Start
                        xlRng.Range("N" & j) = E.Finish
                        j = j + 1
                    Next E
            End If
        End If
    Next r
    MyXL.ActiveWorkbook.Worksheets("Exception Report").Columns("A:N").AutoFit
    End Sub

    John

    Tuesday, October 7, 2014 10:38 PM

All replies

  • Nate,

    That's the main issue with using one of the change Events, they trigger for nearly everything. As far as I know, there is no "collection" of task changes so that idea goes out the window. And you're not in a loop, so you can't simple tag the first change and exit the loop. However, here's an off-the-wall idea that may work in your scenario. When a change occurs, particularly as a result of link flowdown, that action will occur in a short length of time, basically the time it takes Project to re-calculate the plan. You could measure that for a typical change using the Timer function. Given that window of time you may be able to set a "time trap" such that a change that triggers multiple change events in a specific period of time from the first instance could be used to suppress the linked changes.

    It sounds like you are using paste links to update Excel from Project. I assume you are aware that paste links are prone to corruption. If I were doing this I would probably incorporate some code in the macro to update Excel and avoid the paste link issue.

    John

    Tuesday, October 7, 2014 4:59 PM
  • Thanks John! I didn't know about the corruption thing! I will certainly look into that. That could be disastrous!

    I like you time trapping idea. I have put another process in place for now that sort of solves the problem. Each time a task is updated I just add to a public counter variable instead of posting a message box. Then, when the user saves the file they will get the warning that X number of tasks have been updated. I paired this with a custom filter and view that shows only the tasks recently updated.

    After having read your response I started thinking about the time it takes Project to recalculate. I see there is an event called ProjectCalculate. I may be able to do something with that instead of relying on the save event.

    What do you think?

    Nate

    Tuesday, October 7, 2014 6:24 PM
  • Nate,

    I like your idea of the counter variable, more than my timer idea. But at least I gave you some food for thought.

    The ProjecCalculate Event fires after a calculation so how about this idea. When that event fires, initiate code that exports the data to Excel along with a message saying the export occurred and perhaps also how many dates were updated. That avoids the corruption issue and gives just the single message for the bulk update.

    Play with it, see what you can do.

    John

    Tuesday, October 7, 2014 7:04 PM
  • I started playing around with the calculate event and found that the even triggers only once for each changed task, regardless of the number of changes caused by link flowdown. This also seems to be true for the task update. it only occurs once for the change in the first task, then it doesn't occur again for tasks that change as a result of link flowdown.

    Just to be clear, there is no way to identify the collection of tasks changing as a result of link flowdown. Is that right?

    I also did a bit of research regarding the potential corruption issue. http://answers.microsoft.com/en-us/office/forum/office_2010-project/link-between-ms-excel-2010-and-ms-project-2010-not/99a2e05e-2f45-4e97-9ce0-eef2866c2c8b

    From this it sounds like the corruption is only an issue if the file names or locations may change. If this is really true then we may be ok with the pasted links since we have a strict policy regarding file names and locations that prevent the files from being moved or renamed.

    Can you point me to a link describing the process of making Excel linked updates via VBA instead of using pasted cell links? I have worked with updating Excel from Access VBA before, but not from Project.

    Thanks again for your help! I think we are on the right track. I just want to make sure I get it right.

    Nate

    Tuesday, October 7, 2014 7:49 PM
  • Nate,

    Yes, as far as I know, there is no "collection" of task changes. Remember, a collection is a group of objects and a change isn't an object.

    I see you found one of the posts I responded to regarding paste links. If you have ABSOLUTE file discipline management, then you might be okay with paste links, but since there is a much better alternative (i.e. VBA) to do what you need I'd avoid the paste link idea. As Clint Eastwood said in his Dirty Harry movie, "Do you feel lucky?"

    There have been various posts with code to export Project data to Excel. Unfortunately I find the search capabilities of these forums to be sorely lacking so rather than a link, here is some sample code you can adapt.

    Option Explicit
    Sub CalendarExceptions()
    'Basic macro code created by Kiran.K and posted on MSDN Project
    ' customizing and programming forum Feb 7,2013
    'Code streamlined and updated by John - Project June 2,2014

    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    Dim i As Integer, j As Integer
    Dim E As Exception
    Dim r As Resource
    Dim xlRng As Range

    'open Excel, define workbook, and set column headers
    MyXL.Workbooks.Add
    MyXL.Visible = True
    MyXL.ActiveWorkbook.Worksheets.Add.Name = "Exception Report"
    MyXL.ActiveWorkbook.Worksheets("Exception Report").Activate
    Set xlRng = MyXL.ActiveSheet.Range("A1")
    xlRng.Range("A1") = "Proj Cal Holidays"
    xlRng.Range("B1") = "Start Date"
    xlRng.Range("C1") = "Finish Date"
    xlRng.Range("E1") = "Res Name"
    xlRng.Range("F1") = "Res Base Cal"
    xlRng.Range("G1") = "Base Cal Excep"
    xlRng.Range("H1") = "Start Date"
    xlRng.Range("I1") = "Finish Date"
    xlRng.Range("K1") = "Resource Name"
    xlRng.Range("L1") = "Res Excep"
    xlRng.Range("M1") = "Start Date"
    xlRng.Range("N1") = "Finish Date"

    'First gather and export Project calendar exceptions
    i = 2
    If ActiveProject.Calendar.Exceptions.Count > 0 Then
        For Each E In ActiveProject.Calendar.Exceptions
            xlRng.Range("A" & i) = E.Name
            xlRng.Range("B" & i) = E.Start
            xlRng.Range("C" & i) = E.Finish
            i = i + 1
        Next
    End If

    'Next, gather and export resource base calendar exceptions along with
    '   resource calendar exceptions
    i = 2
    For Each r In ActiveProject.Resources
        If Not r Is Nothing Then
            j = i
            If r.Type = pjResourceTypeWork Then
                    For Each E In r.Calendar.BaseCalendar.Exceptions
                        xlRng.Range("E" & i) = r.Name
                        xlRng.Range("F" & i) = r.Calendar.BaseCalendar.Name
                        xlRng.Range("G" & i) = E.Name
                        xlRng.Range("H" & i) = E.Start
                        xlRng.Range("I" & i) = E.Finish
                        i = i + 1
                    Next E
                    For Each E In r.Calendar.Exceptions
                        xlRng.Range("K" & j) = r.Name
                        xlRng.Range("L" & j) = E.Name
                        xlRng.Range("M" & j) = E.Start
                        xlRng.Range("N" & j) = E.Finish
                        j = j + 1
                    Next E
            End If
        End If
    Next r
    MyXL.ActiveWorkbook.Worksheets("Exception Report").Columns("A:N").AutoFit
    End Sub

    John

    Tuesday, October 7, 2014 10:38 PM
  • Hi,

    Like John I recommend you don't use linked cells. too corruption prone.

    There is a way to stop extra interrupt prompts. Create a variable using:

    Public ExitEvent as Boolean

    At the top of a module (before any sub). This is a global variable. Set it to true at the beginning of first interrupt:

    Private Sub App_ProjectBeforeTaskChange(ByVal tsk As Task, ByVal Field As Long, ByVal NewVal As Variant, Cancel As Boolean)
    
    'Code for managing 100% complete and Date Completed
        If ExitEvent then
            Exit Sub
        End If
    
        ExitEvent=True
    '(This part works OK, see the next section for the code I need to modify)
        If ((Field = pjTaskPercentComplete) And (NewVal = 100)) Then
            'Add today's date to completed date field
            'MsgBox "Adding today's date: " & Date & " to the Date2(DateCompleted) field."
            tsk.Date2 = Date
        End If
        If ((Field = pjTaskPercentComplete) And (NewVal = 0)) Then
            'Remove date from completed date field
            'MsgBox "Removing date from the Date2(DateCompleted) field."
            tsk.Date2 = ""
        End If
    
    'Code for managing warnings to user if PST dates will change.
    ' Currently this message is triggered for each task that causes a change.
    ' Can we only trigger this task once for each group of changed dates?
    
        If ((Field = pjTaskStart) Or (Field = pjTaskFinish)) Then
            If (insrt(tsk.Text15, "PST") <> 0) Then
                MsgBox "Warning! You have changed a date that is linked to a line in the PST. As a result the PST has been automatically updated."
            End If
        End If
        ExitEvent=False
    End Sub

    All subsequent calls will now be exited until you reset ExitEvent to False at the end of the event. I use this technique regularly.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, October 8, 2014 9:06 AM
    Moderator
  • Thanks guys!

    I want to avoid writing VBA to update Excel from Project for now (even though you provided some great starting code).

    To sum up my understanding: DDE links from Project into Excel may cause corruption if the Project or Excel files are renamed or moved. Right?

    • Are there other scenarios that result in corruption?
    • Does the corruption affect the data within Project, or just the link between Project and Excel (thus preventing proper updates)?
    • Can we be sure that if the file names never change, and the file locations never change, that the links and data will not become corrupted?

    We keep our master Project file on a shared network folder. Lots of people open it to run views and filters, but I am the only one that makes changes or updates to the file. I do this by copying the Project file to my desktop, making the updates, and then overwriting the original file on the network. The Excel file that is automatically updated from Project is also located on a shared network folder. Unlike the Project file, users open this file directly from the network to make changes. Anyone can make changes to the Excel file. I will lock the date cells in the Excel file to prevent manually updating the dates in Excel, thus forcing users to update the dates via Project.

    Is this a scenario that could cause corruption?

    Is there more information on this issue?

    Thanks for your help! :)

    Nate

    Wednesday, October 8, 2014 6:29 PM
  • That last post was more about the DDE corruption issue. I have another question.

    The BeforeTaskChange event only seems to occur for the first task that is changed. Any tasks changed as a result of link flowdown do not seem to trigger the event. Am I missing something?

    If not, is there some other event that is triggered for link flowdown updates?

    Thanks again!!

    Nate

    Wednesday, October 8, 2014 6:43 PM
  • Yes. Every time you over-write the file the dice roll! What gets corrupted is one or more files, not links, to the extent you may not be able to open them again.

    If the shared network is part of a Wide Area Network (WAN) then the risks go up again. You need to create a solution with NO inter-file links for reliable use.

    The other issue is that links between Excel and Project aren't that useful. Add or remove a task in Project and you need to re-build all links again. DDE doesn't automatically add or remove links to match the inserted or deleted tasks. In fact if you delete a task you end up with a corrupt link hanging around that may add to file corruption risk, but I don't know if that's true. In any case, for safety delete links to tasks that are about to be deleted before deleting the task. Ditto in Excel if you want to insert or deleted cells, break the links first.

    As a go between, save an Excel version of your schedule each week for people to use for reporting. Updated Excel files can be merged back to update Project, but you will need to experiment with what updates work and what doesn't.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, October 8, 2014 8:52 PM
    Moderator
  • Correct, only edited task generates an event.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, October 8, 2014 8:53 PM
    Moderator
  • Thanks Rod. This is all great information. I will begin looking into a way to update Excel from Project without using links. I did a few experiments and found that corruption is really quite easy given many of the scenarios you described.

    Once I complete the code, would it be helpful to post it back here for the benefit of future Project programmers?

    Thanks for all your help!

    Nate

    Thursday, October 9, 2014 12:42 PM