在 MS Project 中，使用 VBA，我想根据 excel 文件的内容在自定义字段之间移动数据

问：

我想移动各种自定义字段的内容，例如 Text1 -> Text2，然后是 Text3 -> Text1。

我可以使用VBA中的一系列条目来做到这一点： 子transfer_test_1（）

Dim t As Task

For Each t In ActiveProject.Tasks
    t.Text2 = t.Text1
    t.Text1 = ""
Next t

CustomFieldRename FieldID:=pjCustomTaskText1, NewName:="test Field"

End Sub

但是，使用 Excel 工作表作为翻译的来源会更优雅。我使用前面的答案作为打开Excel工作表并将其读取到数组中的基础，以便我可以遍历数组。

Sub GetValuesFromExcel()
'from https://stackoverflow.com/questions/66766996/how-to-pull-project-info-from-excel-into-ms-project-using-a-ms-project-macro

'code uses early binding to the Excel object library so you'll need to set a reference to
'that file (Tools Menu: References, check the box for the Microsoft Excel Object Library).


    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    
    Dim wbk As Excel.Workbook
    Set wbk = xl.Workbooks.Open("C:\Users\miles\OneDrive\Field Translations.xlsx", UpdateLinks:=False, ReadOnly:=True)
    
    Dim Dept As String
    Dim Customer As String
    Dept = wbk.Worksheets("Sheet1").Range("A2")
    Customer = wbk.Worksheets("Sheet1").Range("B2")
    
    'count how many rows
    lastrow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'lastrow = Worksheets("Sheet1").Range("A1000").End(xlUp).Row
    
    Dim DataArray As Variant
    DataArray = Worksheets("Sheet1").Range("A2:d" & lastrow)
    
    wbk.Close False
    xl.Quit
    For r = 1 To lastrow - 1
        For c = 1 To 4
            Debug.Print DataArray(r, c)
        Next c
    Next r
Dim t As Task

For Each t In ActiveProject.Tasks
Debug.Print "test of progress: " & t.ID & " - " & t.Name
    For r = 1 To lastrow - 1
        t.DataArray(r, 2) = t.DataArray(r, 1)
        t.DataArray(r, 1) = ""
    Next r
Next t
'For r = 1 To lastrow - 1
'    CustomFieldRename FieldID:=pjCustomTask & DataArray(r, 2), NewName:=DataArray(r, 4)
'Next r

    'ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("Project Departments"), Dept
    'ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("Customer"), Customer
    
End Sub

代码失败，我怀疑它正在“读取”为 t。text2“ = t.”text1“不起作用，但这只是一个猜测！t.DataArray(r, 2) = t.DataArray(r, 1)

谁能建议我如何做到这一点？这种“优雅”的解决方案比将所有翻译直接输入VBA花费更多时间！但是，尝试自动化时经常会出现这种情况，所以我宁愿不放弃！:)

非常感谢。