How to import to the Project's Description and Owner field and Tasks' Status Manager fields into MS Project from Excel using VBA

Question

Thanks to Rachel Hettinger's assistance I've been able to create a VBA macro to open an Excel workbook and create a new project from a worksheet. I am able to import to almost all of the project-level and task-level fields I need, but I did hit a roadblock. cannot import data to the project-level Description and Owner fields that I see in the Project Web App or the Task-level Status Manager field that I see in the client.

The following is an excerpt of how I handle the task-level fields:

MapEdit Name:="Map ", Create:=True, OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True, _
    TableName:=ws.Name, FieldName:="Name", ExternalFieldName:="Task_Name", ExportFilter:="All Tasks", ImportMethod:=0, _
    HeaderRow:=True, AssignmentData:=False, TextDelimiter:=Chr$(9), TextFileOrigin:=0, UseHtmlTemplate:=False, IncludeImage:=False
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Outline Level", ExternalFieldName:="Task_Outline_Level"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Created", ExternalFieldName:="Task_Created_Date"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Start", ExternalFieldName:="Task_Start_Date"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Finish", ExternalFieldName:="Task_Finish_Date"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="% Complete", ExternalFieldName:="Task_Percent_Complete"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Notes", ExternalFieldName:="Task_Notes"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Board Status", ExternalFieldName:="Task_Board_Status"

FileOpenEx Name:=xlFile2, ReadOnly:=False, Merge:=0, FormatID:="MSProject.ACE.14", map:="Map ", DoNotLoadFromEnterprise:=True

And this is a very limited excerpt of how I handle the project-level fields:

'Project Departments
x = 0       
For x = 1 To wbk.ActiveSheet.Range(colRangeAddress).Columns.Count
    If wbk.ActiveSheet.Cells(1, x) = "Directorate" Then
        dept = wbk.ActiveSheet.Cells(2, x).Value
        Exit For
    End If
Next x

'Customer
x = 0           
For x = 1 To wbk.ActiveSheet.Range(colRangeAddress).Columns.Count
    If wbk.ActiveSheet.Cells(1, x) = "Project_Customer" Then
        customer = wbk.ActiveSheet.Cells(2, x).Value
        Exit For
    End If
Next x
   
ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("Project Departments"), dept
ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("Customer"), customer

Application.FileSaveAs Name:=myPath & projName, FormatID:="MSProject.mpp" 'Name:="<>\" & projName to send to server

As I mentioned, I cannot import data to the project-level Description and Owner fields that I see in the Project Web App or the Task-level Status Manager field I see in the client. Here's where I see the Description and Owner fields in PWA:

And the Status Manager field in the client:

I tried importing to these fields the same ways as the others that I successfully imported to, but was not able. How can I import these three fields using VBA?

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2021-04-01T20:54:35

The project-level Description and Owner fields exist just in PWA and not in the schedule, so they can't be updated using VBA. It should be possible to update the database directly, but that involves a lot more effort. Speak to your dba about how to go about that.

The Status Manager field is updatable via VBA but not on the Project Summary Task. Use the same code, but at the task level. For example:

ActiveProject.Tasks(5).SetField FieldNameToFieldConstant("Status Manager"), mgr

How to import to the Project's Description and Owner field and Tasks' Status Manager fields into MS Project from Excel using VBA

1 Answers