Consulting

Results 1 to 5 of 5

Thread: Compare two versions of a Project file

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Post Compare two versions of a Project file

    At one point or another, most Project users end up having to compare two Project files to find the differences. This can be a pain in the neck if you have to do it line by line. And writing a macro to do it is no fun either. So, don't do either one - use this macro already finished and tested. It creates a comparison file in Excel and highlights the differences in red. To install it, copy the code into a new module and add a reference to the Excel Object Library. Then load the oldest of the two files to be compared, and then the later one. Then start the macro and sit back until it finishes. Comparing two files has never been easier!

    Here it is - enjoy! Send me your comments or suggestions, please!

    Sub CompareTwoProjectFiles()
    'This VBA macro for Project was written by Rick Williams
    Dim x As Integer
    Dim First As Tasks
    Dim Second As Tasks
    Dim UniqueVal As String
    Dim Match(100000) As Boolean
    Dim Deletes(100000) As String
    Dim NumDeletes As Integer
    Dim Adds(100000) As String
    Dim NumAdds As Integer
    Dim Changes(100000) As Integer
    Dim UniqueIdentifier As String
    Dim OrigFile As String
    Dim NewFile As String
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim XLSheet As Excel.Worksheet
    Dim oTask As Task
    Dim i As Long
    x = Application.Projects.Count
    If x <> 2 Then
    MsgBox "You must have two projects open to execute a comparison. You have " & x & " open at this time."
    Exit Sub
    Else
    OrigFile = Application.Projects(1).Path & "\" & Application.Projects(1).Name
    NewFile = Application.Projects(2).Path & "\" & Application.Projects(2).Name
    UniqueIdentifier = InputBox("Which field uniquely identifies tasks?", "Input Unique Identifier", "UniqueID")
    Set xlApp = CreateObject("Excel.Application", "")
    xlApp.Visible = False
    xlApp.Workbooks.Add
    Set xlWorkBook = xlApp.ActiveWorkbook
    Set XLSheet = xlApp.ActiveSheet
    xlApp.Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = "Comparison between project " & OrigFile & " (Project 1) and " & NewFile & " (Project 2)"
    xlApp.Range("A6").Select
    xlApp.ActiveCell.FormulaR1C1 = "Project"
    xlApp.Range("B6").Select
    xlApp.ActiveCell.FormulaR1C1 = "Task"
    xlApp.Range("C6").Select
    xlApp.ActiveCell.FormulaR1C1 = "Name"
    xlApp.Range("D6").Select
    xlApp.ActiveCell.FormulaR1C1 = "WBS"
    xlApp.Range("E6").Select
    xlApp.ActiveCell.FormulaR1C1 = "Duration"
    xlApp.Range("F6").Select
    xlApp.ActiveCell.FormulaR1C1 = "Preds"
    xlApp.Range("G6").Select
    xlApp.ActiveCell.FormulaR1C1 = "ES"
    xlApp.Range("H6").Select
    xlApp.ActiveCell.FormulaR1C1 = "EF"
    xlApp.Range("I6").Select
    xlApp.ActiveCell.FormulaR1C1 = "BaselineWork"
    xlApp.Range("J6").Select
    xlApp.ActiveCell.FormulaR1C1 = "BaselineCost"
    xlApp.Range("K6").Select
    xlApp.ActiveCell.FormulaR1C1 = "Work"
    xlApp.Range("L6").Select
    xlApp.ActiveCell.FormulaR1C1 = "Cost"
    xlApp.ActiveCell.Offset(1, -11).Range("A1").Select
    Set First = Application.Projects(1).Tasks
    Set Second = Application.Projects(2).Tasks
    Select Case UniqueIdentifier
    Case "UniqueID"
    For Each Task In First
    If Not (Task Is Nothing) Then
    UniqueVal = Trim(Task.UniqueID)
    xlApp.ActiveCell.FormulaR1C1 = "1"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = UniqueVal
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Name
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.WBS
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (Task.Duration / 480)
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Predecessors
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyStart
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyFinish
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.BaselineWork
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.BaselineCost
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Work
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Cost
    ' XLApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.Offset(1, -11).Range("A1").Select
    For Each oTask In Second
    If Not (oTask Is Nothing) Then
    If Trim(oTask.UniqueID) = UniqueVal Then
    xlApp.ActiveCell.FormulaR1C1 = "2"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    Match(Task.UniqueID) = True
    xlApp.ActiveCell.FormulaR1C1 = oTask.UniqueID
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Name
    If oTask.Name <> Task.Name Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.WBS
    If oTask.WBS <> Task.WBS Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (oTask.Duration / 480)
    If oTask.Duration <> Task.Duration Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Predecessors
    If oTask.Predecessors <> Task.Predecessors Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyStart
    If oTask.EarlyStart <> Task.EarlyStart Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyFinish
    If oTask.EarlyFinish <> Task.EarlyFinish Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.BaselineWork
    If oTask.BaselineWork <> Task.BaselineWork Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.BaselineCost
    If oTask.BaselineCost <> Task.BaselineCost Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Work
    If oTask.Work <> Task.Work Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Cost
    ' XLApp.ActiveCell.Offset(0, 1).Range("A1").Select
    If oTask.Cost <> Task.Cost Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(1, -11).Range("A1").Select
    End If
    End If
    Next
    If Not Match(Task.UniqueID) Then
    NumDeletes = NumDeletes + 1
    Deletes(NumDeletes) = Task.UniqueID
    End If
    End If
    Next
    For Each Task In Second
    If Not (Task Is Nothing) Then
    UniqueVal = Trim(Task.UniqueID)
    For Each oTask In First
    If Not (oTask Is Nothing) Then
    If Trim(oTask.UniqueID) = UniqueVal Then
    Match(Task.UniqueID) = True
    End If
    End If
    Next
    If Not Match(Task.UniqueID) Then
    NumAdds = NumAdds + 1
    Adds(NumAdds) = Task.UniqueID
    End If
    End If
    Next
    Case "Text1"
    For Each Task In First
    UniqueVal = Trim(Task.Text1)
    xlApp.ActiveCell.FormulaR1C1 = "1"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = UniqueVal
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Name
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.WBS
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (Task.Duration / 480)
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Predecessors
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyStart
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyFinish
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.LateStart
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.LateFinish
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.FreeSlack
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.TotalSlack
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.Offset(1, -12).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = "2"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    For Each oTask In Second
    If Trim(oTask.Text1) = UniqueVal Then
    Match(Task.Text1) = True
    xlApp.ActiveCell.FormulaR1C1 = oTask.Text1
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Name
    If oTask.Name <> Task.Name Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.WBS
    If oTask.WBS <> Task.WBS Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (oTask.Duration / 480)
    If oTask.Duration <> Task.Duration Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Predecessors
    If oTask.Predecessors <> Task.Predecessors Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyStart
    If oTask.EarlyStart <> Task.EarlyStart Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyFinish
    If oTask.EarlyFinish <> Task.EarlyFinish Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.LateStart
    If oTask.LateStart <> Task.LateStart Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.LateFinish
    If oTask.LateFinish <> Task.LateFinish Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.FreeSlack
    If oTask.FreeSlack <> Task.FreeSlack Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.TotalSlack
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    If oTask.TotalSlack <> Task.TotalSlack Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.Text1) = Changes(Task.Text1) + 1
    End If
    End If
    Next
    If Not Match(Task.Text1) Then
    NumDeletes = NumDeletes + 1
    Deletes(NumDeletes) = Task.Text1
    End If
    xlApp.ActiveCell.Offset(1, -12).Range("A1").Select
    Next
    For Each Task In Second
    UniqueVal = Trim(Task.Text1)
    For Each oTask In First
    If Trim(oTask.Text1) = UniqueVal Then
    Match(Task.Text1) = True
    End If
    Next
    If Not Match(Task.Text1) Then
    NumAdds = NumAdds + 1
    Adds(NumAdds) = Task.Text1
    End If
    Next
    Case "ID"
    For Each Task In First
    If Not (Task Is Nothing) Then
    UniqueVal = Trim(Task.UniqueID)
    xlApp.ActiveCell.FormulaR1C1 = "1"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = UniqueVal
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Name
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.WBS
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (Task.Duration / 480)
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Predecessors
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyStart
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyFinish
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.LateStart
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.LateFinish
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.FreeSlack
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.TotalSlack
    ' XLApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.Offset(1, -11).Range("A1").Select
    For Each oTask In Second
    If Not (oTask Is Nothing) Then
    If Trim(oTask.UniqueID) = UniqueVal Then
    xlApp.ActiveCell.FormulaR1C1 = "2"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    Match(Task.UniqueID) = True
    xlApp.ActiveCell.FormulaR1C1 = oTask.UniqueID
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Name
    If oTask.Name <> Task.Name Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.WBS
    If oTask.WBS <> Task.WBS Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (oTask.Duration / 480)
    If oTask.Duration <> Task.Duration Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Predecessors
    If oTask.Predecessors <> Task.Predecessors Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyStart
    If oTask.EarlyStart <> Task.EarlyStart Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyFinish
    If oTask.EarlyFinish <> Task.EarlyFinish Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.LateStart
    If oTask.LateStart <> Task.LateStart Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.LateFinish
    If oTask.LateFinish <> Task.LateFinish Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.FreeSlack
    If oTask.FreeSlack <> Task.FreeSlack Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.TotalSlack
    ' XLApp.ActiveCell.Offset(0, 1).Range("A1").Select
    If oTask.TotalSlack <> Task.TotalSlack Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.UniqueID) = Changes(Task.UniqueID) + 1
    End If
    xlApp.ActiveCell.Offset(1, -11).Range("A1").Select
    End If
    End If
    Next
    If Not Match(Task.UniqueID) Then
    NumDeletes = NumDeletes + 1
    Deletes(NumDeletes) = Task.UniqueID
    End If
    End If
    Next
    For Each Task In Second
    If Not (Task Is Nothing) Then
    UniqueVal = Trim(Task.UniqueID)
    For Each oTask In First
    If Not (oTask Is Nothing) Then
    If Trim(oTask.UniqueID) = UniqueVal Then
    Match(Task.UniqueID) = True
    End If
    End If
    Next
    If Not Match(Task.UniqueID) Then
    NumAdds = NumAdds + 1
    Adds(NumAdds) = Task.UniqueID
    End If
    End If
    Next
    Case "WBS"
    For Each Task In First
    UniqueVal = Trim(Task.WBS)
    xlApp.ActiveCell.FormulaR1C1 = "1"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = UniqueVal
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Name
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.WBS
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (Task.Duration / 480)
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.Predecessors
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyStart
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.EarlyFinish
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.LateStart
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.LateFinish
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.FreeSlack
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = Task.TotalSlack
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.Offset(1, -12).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = "2"
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    For Each oTask In Second
    If Trim(oTask.WBS) = UniqueVal Then
    Match(Task.WBS) = True
    xlApp.ActiveCell.FormulaR1C1 = oTask.WBS
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Name
    If oTask.Name <> Task.Name Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.WBS
    If oTask.WBS <> Task.WBS Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = (oTask.Duration / 480)
    If oTask.Duration <> Task.Duration Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.Predecessors
    If oTask.Predecessors <> Task.Predecessors Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyStart
    If oTask.EarlyStart <> Task.EarlyStart Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.EarlyFinish
    If oTask.EarlyFinish <> Task.EarlyFinish Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.LateStart
    If oTask.LateStart <> Task.LateStart Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.LateFinish
    If oTask.LateFinish <> Task.LateFinish Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.FreeSlack
    If oTask.FreeSlack <> Task.FreeSlack Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = oTask.TotalSlack
    xlApp.ActiveCell.Offset(0, 1).Range("A1").Select
    If oTask.TotalSlack <> Task.TotalSlack Then
    xlApp.ActiveCell.Select
    With xlApp.Selection.Font
    .FontStyle = "Bold"
    .ColorIndex = 46
    End With
    Changes(Task.WBS) = Changes(Task.WBS) + 1
    End If
    End If
    Next
    If Not Match(Task.WBS) Then
    NumDeletes = NumDeletes + 1
    Deletes(NumDeletes) = Task.WBS
    End If
    xlApp.ActiveCell.Offset(1, -12).Range("A1").Select
    Next
    For Each Task In Second
    UniqueVal = Trim(Task.WBS)
    For Each oTask In First
    If Trim(oTask.WBS) = UniqueVal Then
    Match(Task.WBS) = True
    End If
    Next
    If Not Match(Task.WBS) Then
    NumAdds = NumAdds + 1
    Adds(NumAdds) = Task.WBS
    End If
    Next
    End Select
    End If
    If NumAdds > 0 Then
    xlApp.ActiveCell.Offset(2, 1).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = "Tasks Added"
    xlApp.ActiveCell.Offset(1, 0).Range("A1").Select
    For i = 1 To NumAdds
    xlApp.ActiveCell.FormulaR1C1 = "Task " & Adds(i) & " was added."
    xlApp.ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
    End If
    If NumDeletes > 0 Then
    xlApp.ActiveCell.Offset(2, 0).Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = "Tasks Deleted"
    xlApp.ActiveCell.Offset(1, 0).Range("A1").Select
    For i = 1 To NumDeletes
    xlApp.ActiveCell.FormulaR1C1 = "Task " & Deletes(i) & " was deleted."
    xlApp.ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
    End If
    Set First = Nothing
    Set Second = Nothing
    xlApp.Visible = True
    End Sub
    Last edited by Aussiebear; 02-19-2022 at 07:48 PM. Reason: Changed code tags

Posting Permissions

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