Opening and using Microsoft Project from an Excel VBA Macro

Anyone who uses Microsoft Project regularly will know all to well that although MSP is powerful it is also sometimes a bit clumsy.  There are quite complex data structures behind MSP and can be difficult to view the data in the way that makes sense of it.  Using Microsoft Excel VBA Macros to extract data from MSP and then manipulate it in Excel is very powerful.  This article shows how to create Excel Macros to do that.

Getting Started

We need to create a Application object and the best way to do this is to use the Early Binding approach which requires that we need to select the Object Library.

Step 1: Select the References menu from Tools

Step 2: Find the Microsoft Office Project Object Library and click OK

Selecting a file to open

This bit of code allows you to select a Microsoft Project file to open.  It opens a dialog box and saves the returned file name into object strFileToOpen.  We can then test to see if a filename was selected or the box was cancelled.

    ' Open a file Dialog to find the project file
    strFileToOpen = Application.GetOpenFilename _
        (Title:="Please choose a file to open", _
        FileFilter:="Project Files *.mpp* (*.mpp*),")
        
    ' Return if no file name selected
    If strFileToOpen = False Then
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        Exit Sub
    End If

Opening the MSP File

Once we have the File name we can create an MS Project Application object and use to manipulate the Project file.  In this very simple example, we open the MS Project file and add a task called "Hello World", save the file and then close it.

    Dim App As MSProject.Application
    Set App = New MSProject.Application
    'App.Visible = True

    Debug.Print "Opening File: ", strFileToOpen
    App.FileOpen strFileToOpen
    Debug.Print "About to Add Task"
    App.ActiveProject.Tasks.Add "Hello World"
    App.FileSave
    App.FileClose
    App.Quit

I have commented out the App.Visible = True statement but you can use this to bring the MS Project application into foreground.  Without this, the code will manipulate the MSP file in the background without displaying it.

Did we Open the MSP Application

The above code is fine but at the end of it we close the MSP application.  From a usability perspective we may want to open MSP in the background and process our Macro if MSP was not already open.  However, if it was open already,  we want to select the file that we are working on and activate it.  

    weOpened = 1
    ourFile = 0
    
    For Each prj In App.Projects
        weOpened = 0
        Debug.Print "Looking through open projects: " + CStr(prj.FullName)
        If prj.FullName = strFileToOpen Then
            Debug.Print "About to Activate the App we opened"
            App.Projects(prj.Name).Activate
            ourFile = 1
        End If
    Next prj

The above bit of code iterates through the list of projects open in MSP.  If there are any open it marks it that we didn't open MSP.  If it finds the same file name from the filename dialog it calls the Activate method do make it the Project in focus.

Download Full File

The full file for the code below can be downloaded from here (It uses a proof of work formula before opening):

MSP VBA - Part 1 - Opening MSP from Excel

About Me

If you like this post then please comment below.  Please also make a small contribution so that I can post more code snippets and samples here.

Bitcoin:      1MhaZvTqftdebwNihisrAbz22zaSG6tFcN

Ethereum:  0xe69b176A1A5492f19E3595389EA094C732723b69