Auto populate project level Priority field based on lookup to Excel file table

Question

I have multiple small plans (managed by PMs) embedded in a master plan file which I manage. The PMs can add tasks to the sub plans as necessary.

After receiving each sub plan and refreshing the master plan I want to be able to update the Project level Priority field via a lookup to a table ("tblPriorityScores") in an Excel file. The lookup field common to both the plan and excel table will be the project file name.

The aim is to then resource level the master plan using these plan level priority scores.

I need to do this after receiving the plans back from the PMs so they are not tempted to alter the priority score for their project.

I have now figured out how to loop through the sub plans in turn and update their Priorities via ActiveProject.ProjectSummaryTask.Priority but now need to figure out how to open the relevant Excel file, lookup the table and return the relevant value for the current active project.

Any suggestions?

Answer 1

You can use MS Project's Import Wizard to merge data from an Excel file into the active project.

With the target project open, do File Open and search for your Excel file. Opening an Excel file within MS Project will launch the Import Wizard and walk you through the steps of building a map (e.g. what fields are in your Excel file), setting a merge key (e.g. how to match up Excel data with Project data; hint: use the task Unique ID), and previewing the data.

The Import Wizard can be used in three ways: creating a new project, appending to the active project, or merging with the active project. Be sure to select the third option.

Of course you could also do this with VBA, but the Import Wizard is incredibly fast and powerful and specifically designed to do this task. At the end of the wizard you can save the map to skip all the setup next time you import the data.

For more information see the msdn page (not great) or this page (much better).

Answer 2

What Rachel suggested did work, but I wanted more transparency in my solution than using the import map process offered, and I enjoy playing with VBA. So this is the VBA code I came up with (largely copied from other sources), which seems to work ok (the variables are all declared as Public in a separate module)

Sub RefreshPriorityScore()
' Populate all the tasks for each sub project with the appropriate Priority score  taken from the ResourceReports file

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")

If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
Else
End If

xlApp.Visible = True

' Open the excel file
    Set xlWB = xlApp.Workbooks.Open(ResourceReportsFolderPath & ResourceReportsFilename) ' Name of Report file
    Set xlWS = xlWB.Worksheets("MSPdataProjects")

    xlWS.Activate
    Set tbl = xlWS.ListObjects("tblMSPdataProjects")

    TableRowsCount = tbl.ListRows.Count

' Capture the Priority Score from Excel and insert into the Master plan file
    For Each Tsk In ActiveProject.Tasks
        PID = Tsk.Text30
        PriorityScore = xlApp.SumIfs(tbl.ListColumns("PriorityScore").DataBodyRange, tbl.ListColumns("ProjectID").DataBodyRange, PID)
        Tsk.Priority = PriorityScore
    Next Tsk

' To tidy up
    xlApp.UserControl = True
    Set xlApp = Nothing

This updates my Master file (the ActiveProject) that contains all the sub projects.