Exporting MPP Files with VBA

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.

image

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.

image

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.

image

Next up….the code to take that directory and load it to a new Project Server instance.

Advertisements
Exporting MPP Files with VBA

8 thoughts on “Exporting MPP Files with VBA

  1. Kai Lemay says:

    Hi Andrew I have a question is it possible to access the published database and export all MPP files via VBA or SQL to a single XML file and then read that file back into a single project file retaining all the links? I am curious as we have some analysis software where we would like to pull the information off the server without having to go through MS Project while retaining the links so it can be imported in the analysis software.

  2. preetam bhoobun says:

    Hi Andrew I’ve written a VBA to export data from MPP files from a folder on a shared drive how would you do that to export MPP files from project server?

  3. Aaron Demenge says:

    This is a great macro! I’m attempting to run it with Project 2013 but the macro will only run on two files and then errors out on the ‘FileSaveAs’ method. Do you know how to resolve this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s