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

Dealing with Cargo Cult mentality in ERP implementations

Cargo Cults have developed several times throughout history, in various locations around the world, after the withdrawal of technologically advanced civilisations at the end of war or military occupation.  The indigenous locals, not understanding the science behind how airplanes full of wonderful cargo were arriving with their advanced and luxury goods, believed that if they repeated the same rituals as the Western serviceman then they would please the gods to deliver the same results.  Tribal locals built mocked up runways and stood at the end waving flags hoping that the planes would arrive with their wonderful cargo.  There are stories of people building wooden radios, control towers and runways all in hope of once again attaining the lavish western goods that were in abundance when, presumably, the foreign servicemen were pleasing their gods.  Their lack of appreciation and understanding of the science and technology resulted in them dealing with the situation in a mythical manner.

During the early days of computing, a similar behaviour occurred within business.  Companies looked at their competitors and saw how profitable they had become after deploying these new things called computers.  They believed that if they also deployed computer systems into their businesses then they would also become more profitable.  Except, they had no idea what computers were, what they really did or how to use them.  They didn't really understand the technology and their adoption of the new technology became almost mythical.

In today's world, this behaviour still exists and in technology you will come across expressions like Cargo Cult Programming which describes where a programmer writes nonsense code that does not actually do anything.

Over the last few years, I have come across similar behaviours whilst working as a ERP programme manager.  Cargo Cult mentality manifests itself in two ways within organisations implementing ERP systems.

First, some companies adopt ERP with very vague ideas regarding the benefits they will gain often based on vague concepts like "consolidated and integrated system" or "global system".  The basic underlying issue here is that they haven't really understood the business case for adopting the ERP solution. In fact, I find it shocking how many projects do not really have a business case or it is just a document that is collecting dust somewhere but is forgotten about one the implementation actually starts. To be successful in deploying ERP we have to have clear benefits that we are trying to realise.  Success needs something to measure against.  Moreover, the business case should be a living document that is checked and updated on every turn of the project.  If we don't, are we any better than the cargo cult mentality adopters of early computers?

Implementation projects also tend to forget the human factor of what it really takes to facilitate the organisational change to realise the benefits stated in the business case.  Too many projects just ram the new system in with a bit of training and then wonder why they struggle to see the benefits.

In my view, to counter this, organisations need to work with an implementation partner who can help them set out their business case from the programme outset and then provide support for the organisation to transform themselves so that they can adopt the new ERP solution successfully.  Finally, we need to measure the benefits and track progress against a plan of how benefits will be realised.

The second form of Cargo Cult mentality that I have witnessed is during the implementation itself.  Many organisations have never embarked on a transformation programme that will impact the organisation to the extent of an ERP implementation.  It may be the largest internal programme they have ever executed.  However, they may look at other organisations who have completed the journey and think that they can easily repeat it by themselves (or with little help).  Or they will mistakenly think that they can just push some of their existing BAU staff into project roles and that they will have sufficient skills to complete the tasks required.  Often seasoned experts are expensive and the organisation will think that they can save money by doing many of the implementation activities themselves. 

 BAU staff don't always adjust well to project roles where they suddenly find themselves moved out of their comfort zone and working in an environment that contains risk and uncertainty.  Programme management teams do not always consider the human aspect and the stress they are putting on their team.  Compare this to specialist consultants who have often completed several implementations and perfected skills through experience and who have usually been burned, bear the scars and have learned from their mistakes.  There is often a sense of naivety as to how easy these skills are to pick up and it is not recognised early enough that the project team are suffering.

The problem is, they don't know what they don't know.  They don't know the pitfalls to look out for.  They don't recognise when the wheels are wobbling.  They run through the programme with a level of confidence that is built upon shaky foundations of ignorance. 

"Ignorance more frequently begets confidence than does knowledge" - Charles Darwin

I am not saying not to use people for the business, in fact they are vital.  Use them for what they are good at which is bringing their knowledge and experience of your business to the table to ensure the solution fits the requirements, the culture and the capabilities of your organisation.  In fact, many organisations will not want to use too many staff as it requires backfilling that can hit operating costs.  Instead, the business may believe it is better to use contractors where they can capitalise on the cost.  The danger is that they take on an army of self-serving contractors who do not really understand the business, cannot leverage output from peers in the organisation with whom they don't have a working relationship, may not even have the required expertise but who are good at self-preservation.  The result is often a dysfunctional team and an unhealthy blame culture.  However, this is seen as a cheaper option rather than working with a single partner who will take a more holistic view of the delivery through a team of experts that are used to working together across different programme functions.  The other problem with contractors is that the organisation is not building lasting knowledge and expertise within their own people.  It is not in the best interest of the contractor to pass on knowledge as it may threaten their own worth.

Problems in the ERP implementation quite often surface when the programme starts testing the new solution.  This is when the programme team find requirements have not been met, migrated data is of poor quality, they have not completed their testing scope and the business are resistant to transform themselves to adopt the solution.  Late projects that are restricted by budget often have to cut back on scope which in reality dilutes the final benefits.  This further dampens the spirits of the team and the enthusiasm of the end users.

It can be frustrating for implementation experts in such a situation as they can often see that the wheels are wobbling but are not empowered to do anything about it.  I recently worked on a programme where as a consultant team we could see loads of issues with the organisation's preparation for UAT but the organisation didn't see any impending disaster.  The programme management didn't listen to our advice and as they didn't know what good should look like, they didn't recognise the signs that things weren't going well.  By refusing to listen to our advice they carried on for a long time marching the path towards failure.  It is not productive to come back and say, "we told you so!"  So how can we influence the situation when the organisation are clearly operating with a Cargo Cult mentality?  That is, they are going through the motions of executing the necessary implementation activities without doing justice to those activities because of their lack of experience.  The stage gates become a box-ticking exercise but then they are surprised when the wheels finally fall off.

If you are noticing Cargo Cult mentality on your implementation project then I would advise to follow the following procedure:

1. Listen to the arguments that the organisation bring as to why they think they can do it easily themselves or as to why they think their approach will bring success.  Be sure you understand their arguments and be certain that you are not the one who is over complicating the situation.  Once you understand their point of view then you can decide the best way to respond.
2. Stake out the stakeholders.  You need to understand which stakeholders are more willing to listen, what their background, influence and experience is, so that you can build a plan for influencing them.
3. Once you understand the different stakeholders then you need to build rapport with them and position your viewpoint with those who have the influence and who are more inclined to listen.
4. Sow seeds - this has to be done without being seen as being overly negative.  We need to provide solutions to perceived problems rather than just moan.
5. Perseverance.

Finally, for organisations that are embarking on an ERP implementation, to avoid Cargo Cult behaviours developing, then I have the following advice:

1. Understand your business case, plan how you will transform your business and adopt the solution to realise the benefits stated in your business case.
2. Have a critical look at your internal capabilities and understand if your team have really worked on large transformation projects and have the functional and technical knowledge to enable success.  Relying heavily on contractors may seem cheap but too many will pile up the risk.  Cutting corners on budget early can often mean paying a lot more in the long run.
3. Be prepared to invest in bolstering your skills where you see the shortfall.  Develop a knowledge transfer plan to bolster your project team's knowledge early on.
4. It may not be wise to partner with a ERP system integrator who do not have wider business consultancy skills but rather use a partner who judges success as delivering an outcome that is in-line with the business case.

Abdul-Wahid Paterson is a director in Hitachi Solutions and works as a programme manager on Microsoft Dynamic implementations.  He has a passion for helping develop the next generation of leaders and empowering everyone to achieve the most out of life.  To find out more about Microsoft Dynamics and how it could help your organisation then please visit the Hitachi Solutions page.