如何用MS Project事件处理器检测任务变更并记录至Excel

我有一个大型MS Project文件，需要借助MS Project事件处理器检测任务的各类变更（名称、日期、工期、链接等），并将变更所在任务行复制粘贴至Excel工作表。计划先从跟踪任务名称字段入手，实现后再类推处理其余字段。

尝试的两种方案 #

• 方案一：将Project数据复制粘贴至Excel并建立链接，MS Project数据变更时Excel链接同步更新。通过MS Project事件处理器调用Excel中的Sub过程，找出变更单元格数据并粘贴到同一工作簿的其他工作表（附带相关信息）。选择MS Project事件处理器的原因是：Excel事件处理器无法识别由MS Project操作引发的Excel数据变更，仅能检测手动修改单元格的操作。
• 方案二（更可行）：直接在MS Project中检测特定字段的变更，将目标任务行复制后直接粘贴至Excel工作表。

相关代码 #

MS Project模块代码 #

Dim myobject As New Class1

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

End Sub

类模块代码 #

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
    
End Sub

当前问题 #

修改MS Project任务名称时，MsgBox能正常弹出提示，但执行TrackchangesP1.Sheets("Sheet1").Range("N2") = "hello world"向Excel特定单元格写入内容时，无报错但未生效。

内容的提问来源于stack exchange，提问作者Tyler