Well, I didn’t deliberately intend to get into a VBA rut, but periodically, I like to clean out some of the half written posts I have lying around, and it turns out that I apparently left a couple VBA posts started and not yet finished. My resurgent interest in VBA of late has also been driven by my efforts to get up to speed in Project Online, and the realization that I can easily use my traditional VBA skills to interact with the Project client, and quickly upload data to my Project Online tenant.
Hence, I wrote this quick VBA script to export all of the MPP files in an existing Project Server instance, and then import the files into another Project Server instance. And here’s the beauty of VBA and the Project client interface……the code is essentially version agnostic….meaning I could use it to do a dump of all of my existing project schedules (to prep for export or to perform a backup) from Project Server 2010 and then upload them using a different code into an online version of Project Server 2013.
I’ll also note that there are much more robust partner solutions that pretty much do the same thing – but better. This code is crude, virtually untested, and has only been used in development environments to generate demos.
Identify the Target List
The first step is to generate a list of the projects you would like to export. The easiest way for me to do this was simply to grab the list of projects from the Project Center.
I simply exported the list to Excel, and then copied the results into a Microsoft Project file. I could have simply run the macro from Excel I suppose, but its simpler for me to just use Project.
Note that I would include custom fields as custom fields in the MPP file if I were planning to transfer them over. In this case, I just want the raw project data.
Add the macro…
Sub ExportProjects() Dim T As Task Dim fName As String For Each T In ActiveProject.Tasks fName = T.Name Application.FileOpenEx Name:="<>\" & fName, IgnoreReadOnlyRecommended:=True Application.FileSaveAs Name:="C:\Exports\" & fName, FormatID:="MSProject.XML" Application.FileCloseEx pjDoNotSave, CheckIn:=True Next T End Sub
…and create a directory on the C drive called “Exports.” We’ll export the project files as XML files into this directory. Why XML? It tends to clean up much of the junk that ends up getting pushed into these files over time. It also removes user customizations to views – which I consider a good thing to do periodically. Change the export format to “MPP” to export as an MPP file. Note however that this increases the chance of the import not working properly.
Run the macro. You may have to click through a couple times if a project opens and throws out a prompt to accept updates, etc.
You should end up with a directory full of XML files.
Next up….the code to take that directory and load it to a new Project Server instance.