There is all too often a need to bulk publish all enterprise projects. For example, a project custom field formula, or perhaps a lookup value, has been modified; that change won’t appear in reports correctly until all the projects are published.
Performing the bulk publish can be achieved by utilizing a small custom PowerShell script or .Net application which typically leverages the Client-Side Object Model (CSOM ) for Project Server/Project Online. However, that might be a little too technical for your comfort.
The purpose of this blog is to present an alternative approach. The BulkPublishMacro.mpp file downloadable in this blog contains a VBA macro that I developed. This macro has been labelled “BulkPublishAllEnterpriseProjects”. This should work with Project Server On-Premise and Project Online.
A special feature of the macro is that it uses the Project Server/Online oData API to first retrieve a list of all project names in the connected PWA instance. It then iterates through that list and attempts to publish all projects. Prior to implementing this feature, it was necessary to manually create a list of the projects and implement logic to consume this list by the macro. I found this technique difficult to work with; especially if project names contained double spaces in them or long dashes.
A benefit of using MS Project to publish projects, as opposed to using a CSOM script, is that the scheduling engine of MS Project performs a more reliable publish. As an example, I have had issues where calculated custom fields were not displaying the correct value until the project was published using MS Project. Publishing via a CSOM script of via the browser interface did not resolve said issues in these somewhat unusual cases.
To perform the bulk-publish, you’ll need to perform the following steps.
- You’ll need to ensure you are in the Administrators security group.
- Download the file to your PC, it doesn’t matter where.
- Ensure there is a C:\Logs folder available for use on your PC.
A log file of the macro’s activity will be saved into this folder.
- Open MS Project whilst ensuring that you connect to the Project Web App instance that requires a bulk publish of all enterprise projects.
Note: Ensure the Project Server URL ends in “…/sitename”. For example, a good URL is “https://client.sharepoint.com/sites/sitename” and a bad URL is “https://client.sharepoint.com/sites/sitename/default.aspx”
- Open the BulkPublishMacro.mpp file.
- Click on the View tab >> click on the Macros button >> select the BulkPublishAllEnterpriseProjects macro >> click on the Run button.
- Be ready to handle any pop-ups presented by MS Project.
For example, if there is a scheduling conflict on a project, MS Project will prompt with a warning and this needs to be manually handled. Once the prompt has been handled, such as clicking on “OK”, the macro will continue on its way.
In an ideal situation, there will be no issues detected by MS Project, and therefore no user intervention will be required; it will be just a matter of waiting for the macro to complete its task, which will be known when a dialogue box is presented to the user advising of such.
There is a little error handling within the macro. Examples are:
- Project is checked out.
An “Unable to checked out project.” message is logged and then the project is closed and then continues onto the next project in the list.
- Project was not able to be opened.
A “Project could not be found/opened.” message is logged and then continues onto the next project in the list.
- Project did not successfully publish or took longer than the wait threshold (which I think I set to around 6 minutes). A “Published failed or took longer than expected. Did not attempt to close project.” The message is logged and then continues onto the next project in the list.
I’m not sure about this step, but you may need to include the following references in the Visual Basic editor window, otherwise, the macro may fail to run.
If you do get an unusual error after trying to run the macro, press <ctrl> + <f11> to open the Visual Basic Editor, and then navigate to Tools >> References. Ensure the references shown in the screenshot attached and ticket.