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?