excelvbams-project

Detect when a change occurs on in an MS project file and log the change in an Excel file


I have a large MS project file. I would like to use the MS project event handler to detect when a change has been made to a task (a name change, date change, duration change, link change, etc.), to copy the line on which the change occurred, and to paste it into an Excel worksheet.

I started with tracking the task name field. If I can get this to work, I can probably get the others to work.

I tried two approaches. First approach was to copy-paste the project data into an Excel file so that it was linked. If I made a change in MS project, the same values would change in the linked Excel sheet. I would then use the MS project event handler to call a Sub in Excel. That sub would find the cell data that changed and paste it into another sheet in the same workbook (along with other relevant info). I would use the MS project event handler since I couldn't get the Excel event handler to notice when a change I made in MS project occurred in the Excel sheet. The Excel event handler only seems to detect a change when I manually change a value in a cell.

The second approach seems the most promising. I would detect when a change was made under a particular field in MS project. Copy the desired line and paste it directly into an Excel sheet.

In an MS Project Module:

Dim myobject As New Class1

Sub Initialize_App()
    
    Set myobject.App = MSProject.Application
    Set myobject.Proj = Application.ActiveProject

End Sub

In a Class

Option Explicit

Public WithEvents App As Application
Public WithEvents Proj As Project
Dim TrackchangesP1 As Workbook
Dim stuff As Worksheet
Dim filepath As String


Private Sub App_ProjectBeforeTaskChange(ByVal tsk As Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)
   
'This event triggers before a task field changes.
 'The entire file path is not shown here, but is present in my code.

    filepath = "C:\...\Track changes P1.xlsm"

     On Error Resume Next
          Set TrackchangesP1 = Workbooks(filepath)
         On Error GoTo 0
    If TrackchangesP1 Is Nothing Then
        ' Workbook is not open. Open it in read-only mode.
        Set TrackchangesP1 = Workbooks.Open(filepath, ReadOnly:=True)
    End If

    If Field = pjTaskName Then
        MsgBox "Task name changed to: " & NewVal
        
     
    'TrackchangesP1.Sheets("Sheet1").test 
'calls a sub in the worksheet which displays a message box, commented 'out for now, this is related to
'my first approach.


    TrackchangesP1.Sheets("Sheet1").Range("N2") = "hello world"
 '''
'I wanted to try to see if I could use a change to a task name in MS 'project to trigger something to be entered into a specific cell in excel. 'It didn't work. There are no errors, but nothing is pasted.
    
    End If
    

Solution

  • This code will track changes in an Excel workbook.

    For the Class1 class module:

    Public WithEvents App As Application
    Public WithEvents Proj As Project
    
    Private Sub Class_Initialize()
    
        Set App = Application
            
    End Sub
    
    Private Sub App_ProjectBeforeTaskChange(ByVal tsk As Task, ByVal Field As PjField _
        , ByVal NewVal As Variant, Cancel As Boolean)
        
        With ChangeLog
            Dim r As Long
            r = .UsedRange.Rows.Count + 1
            .Cells(r, 1) = Now
            .Cells(r, 2) = tsk.UniqueID
            .Cells(r, 3) = tsk.Name
            .Cells(r, 4) = Application.FieldConstantToFieldName(Field)
            .Cells(r, 5) = NewVal
        End With
        
    End Sub
    

    For the Module1 module:

    Public myobject As New Class1
    
    Public xlApp As Excel.Application
    Public TrackchangesP1 As Excel.Workbook
    Public ChangeLog As Excel.Worksheet
    Public stuff As Excel.Worksheet
    
    Public Const filepath As String = "C:\....xlsx"
    
    
    Sub StartEvents()
    
        Set myobject.App = Application
        
        InitExcel
        
    End Sub
    
    Sub InitExcel()
    
        On Error Resume Next
        
        If xlApp Is Nothing Then
            Set xlApp = GetObject(, "Excel.Application")
            If xlApp Is Nothing Then
                Set xlApp = CreateObject("Excel.Application")
            End If
        End If
    
        If Not xlApp.Visible Then
            xlApp.WindowState = xlMinimized
            xlApp.Visible = True
        End If
        
        If TrackchangesP1 Is Nothing Then
            Set TrackchangesP1 = xlApp.Workbooks.Open(filepath)
        End If
        If ChangeLog Is Nothing Then
            Set ChangeLog = TrackchangesP1.Worksheets("Sheet1")
        End If
        
    End Sub
    

    And finally, in the ThisProject module:

    Private Sub Project_Open(ByVal pj As Project)
    
        Call Module1.StartEvents
    
    End Sub
    

    Note: Be sure to save the workbook at some point.