Background Information
After adding new enterprise custom field containing a formula, Project Server does not automatically recalculate the formula in all existing projects. This is because each project must be opened individually, the calculation must be performed, and then the project must be saved.
Resolution
The manual resolution is for each project manager to open every project plan that he/she manages, press F9 to manually recalculate the project, and then save the project. This process is a tedious operation if you have many projects. An alternative is to create a macro that automates this process.
Before you create the macro, you need the list of projects in a file.
1. Open SQL Query Analyzer.
2. Change the database to ProjectServer (or the appropriate MSP database).
3. Run the query “Select proj_name from msp_projects where proj_type=0”
4. Click in the results pane.
5. Press Ctrl-A to select all of the data.
6. Right click and select Save As.
7. Enter Projects for the File Name.
8. Select a location such as c:\temp.
9. Click Save.
Now you can create the actual macro.
1. Open up MS Project Pro and connect to your server.
2. Go to Tools – Macro – Visual Basic Editor.
3. Add the following code to a blank project:
Sub Update()
Dim strProj As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set Projs = fs.GetFile("c:\temp\Projects.csv")
Set ts = Projs.OpenAsTextStream
'Steps thru each project in the file
Do Until ts.AtEndOfStream
strProj = ts.ReadLine
FileOpen "<>\" & strProj 'Opens enterprise project
Application.CalculateProject
'application.PublishAllInformation 'uncomment if you need to also publish the plans
FileClose pjSave
Loop
End Sub
4. Run the code.
Note: This could take a very long time to run. The code also assumes that all projects are checked-in.