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.

Project kick-offs and first impressions

The old adage of not judging a book by its cover is something that we are much better at saying than actually doing.  We all fall into the trap of very quickly stereotyping people we meet based on what they look like, how they speak and their mannerisms.  It is important to recognise when performing a project kick-off meeting; this is how people will judge you too.  They will quickly make judgements about what type of person you are and whether they like you or not.  Opinions are formed very quickly but can take time to change.

As project managers and business analysts we attend many project kick-off in our careers where we are often meeting the stakeholders for the first time.  We want to establish ourselves in a position as trusted advisers and to do that, we need to hit the ground running and be completely prepared for project kick-off session knowing that these very first impressions are vital.  We need to be clear as to what we want out of the kick-off session and play the part required for us to deliver success.

"All the world's a stage, And all the men and women merely players; They have their exits and their entrances." - Shakespeare

Playing the part does sound very theatrical and in many ways it is.  Try to imagine the type of person you want others in the kick-off meeting to see you like and then act out being that person.  I am sure you want to come across as confident, knowledgeable and as someone who has done this before.  You need to establish trust.

These are the steps I go through for that very first engagement meeting on the project to try to ensure that the first impressions are positive and to establish trust early in the engagement.

1. Be Prepared - I know, my inner Scout coming out.  Seriously, the first thing to do way before you arrive for that kick-off meeting is to research the most you can about who you are meeting in the kick-off session and be clear about what you intend to get across to them as a message.  If this is the first time you are meeting stakeholders then look them up on LinkedIn or search elsewhere to find out what you can about them.  This will help you also to remember their names when you are introduced  and may give you an ice breaker conversation; such as where they schooled.  People love the sound of their own names so make sure you learn them beforehand so that you can use them when you meet.  Also, you must know what key messages you want to get across.  This is a kick-off session, so not everything is going to stick especially if this is their first exposure to the project for the attendees.  Understand your audience and pick your messages carefully.  Don't completely overload them with too much which may leave them walking away perplexed.
2. Dress to impress - Research shows that the clothes you wear change the way you actually perform.  We need to feel sure of ourselves and portray a confident image.  Wearing the right clothes is like assuming the write costume of an actor entering stage.  However, make sure you know your audiences as the dress code in different organisations can be quite particular.  You do not want to over dress or under dress for the occasion.  The right attire will also impact your own psychology and can make you feel more confident and secure.
3. Psych - I know this makes it sound like you are going into a boxing match rather than a meeting but you need to be in the right state of mind.  To master personal effectiveness we need to control our emotions and be in the appropriate emotional state for the job. If you are busy stressing about things or feeling depressed and down - you need to snap out of it.  You need to approach your meeting with confidence without arrogance; calmness without indifference; and positivism without insincerity.  Negative attitudes will be quickly picked up by others so whatever is troubling you from home - forget it.
4. Smile - "Smile and the world smiles with you", as they say. Always greet people with a smile but make sure the smile is sincere as forced smiles will be picked up as a negative attitude. People generally like happy and positive people and before you even open your mouth to say anything, people will react positively to a facial image that represents happiness.
5. Ice breaker - In many project kick-offs, the people you will be meeting may be feeling anxious because they have never been involved in this type of project before.  It is not only new for you - it is new for them too.  One of the best ways of lightening the mood is to have an ice breaker ready.  Knowing your audience will help here but good  conversational subjects are usually sport, tech gadgets or bizarre stories in the news.  Try to keep away from subjects that could get emotional; such as politics.  Unless talking about Trump classifies as bizarre.  Remember also that people like talking about themselves so if the ice breaker results in someone opening up and divulging a personal story, then make sure you listen attentively as this will give them a good feeling of being accepted.  Building a personal relationship is critical in the journey to becoming a trusted adviser so make an effort to complete this stage.  Lighten the mood before jumping straight into the gory project details and you will feel the audience is more receptive.
6. Be adaptive - it is always difficult when meeting people for the first time to understand their background, experience, attitude and level of understanding.  It may be what you wanted to pitch in the kick-off is going right over their heads.  Or perhaps, unknowing to you, the kick-off participants already have a negative attitude towards the project before you even start and what you are saying is only irritating them further.  You need to watch their body language and tell-tale signs as to whether they are engaged with you or not.  If you losing the plot, then adjust it and change tact.
7. Be self-conscious - You need to pay attention to the pace of your speech, the tone, your mannerisms and your body language.  Speaking to quickly or in a raised tone can give across the message that you are anxious and not confident.  Look at the body language of others and mirror them.  That is, if everyone is sitting very straight backed and formally then do not slouch in front of them.  If they have taken a more relaxed posture then do not sit there all stiff as it will come across as arrogance.
8. Conclude - When concluding the kick-off meeting summarise the key points, review any actions taken and finish on an affirmative message.  For example, let them know how excited you are to have this opportunity to work with them on this project and that you are looking forward to the next steps.  Always finish on a high.
9. Follow-up - Always send a thank you message to follow-up from the meeting thereby again reaffirming the positive messages of commitment.  If you have taken actions during the meeting, you must make sure you complete them and notify the attendees.  One of the rules of becoming a trusted adviser is that you do what you said you were going to do.  It takes time to build trust but can be destroyed quickly so at least make sure this early engagement is off on the right track.

Although I wrote these 9 points with a project kick-off session in mind based on my years of experience in kicking off ERP projects, most of these points could be applied to various other meetings where you meet people for the first time.  For example, for an interview you may wish to follow all these points although you hopefully would not pick up any actions.

I would be interested to here your thoughts and ideas of how to make these first meetings effective and better received by the attendees.


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.

Project efficiency - Thinking outside the box

I saw this picture of a young child's answer that clearly misunderstands what the obvious intent was in the instruction given in the question.  Although, if you read it carefully they did literally what they were asked.  It just wasn't the expected conformist norm.  It made me ponder as to why so many people find it difficult to think outside the box.  We are very conformist to what has already been done in the past, we are reluctant to embrace change.

"Our desire to conform is greater than our respect for objective facts." - Margaret Drabble

I see this on ERP projects the whole time where conformist views and resistance to change, rear their heads across different areas of the project.

From an ERP implementation perspective, the team like to follow a methodology that is tried and tested.  I am of course not saying that that in itself is a bad thing as we do need repeatable processes in ERP implementations.  However, things in the world around us move on and there are new technologies available and new thoughts regarding effective ways of working.  There are new tools, methods and models that we could utilise.  Yet we become used to doing things in the way they have always have been done and feel uncomfortable to challenge the status quo.

In my view, we should always be looking to see how we can improve our processes and should be looking forward as to how we can do things better.  We need to "sharpen the saw" to make ourselves more efficient and therefore should always embrace good change.  For example, cloud has brought in a whole new set of tools and techniques that we can add to our arsenal and we should be evaluating how we can use cloud to make our work more efficient.  It is therefore vital that we hold regular lessons learned sessions to review what we did well and what did not go so well.  We should examine improvements against what is new in our environment that could help us change the way we work for the better.  Too many projects lack vital lesson learned sessions or they conduct them as tick-box exercises without really driving through the required obvious change.  The objective facts in front of them.

Of course, change has to be backed up by a valid business case that takes into account all benefits, costs and risks associated with the change.  It may be that your project is too near its end to make it worthwhile to change.  Also, too much change at one time can cause too much burden on the project and as such the project leadership always needs to look at change in respect to other changes.

In ERP, we see resistance from the business to change the way they have always done things.  Process re-engineering is hard work and the change needs proper senior stakeholder engagement.  It requires a drive from above.  Otherwise, you can have the best organisational change management team and the best business process team, but you will not be able to make real change if the senior executives are not fully supporting it.  Most people will resist change - ignore the objective facts and instead conform to what has always been the way.

Of course, this resistance to change and conforming to the status quo is not restricted to ERP implementations.  Projects by definition involve change and to drive project efficiency we should all think out the box and try to break the chains of conformity.  One of the ways to do this is to always ask ourselves the question "why?"  Why do we do we run this process in this way?  Why do we use this particular IT system?  Why have we put these controls in place?  We should continue asking ourselves why in a similar approach to the 5 whys root cause analysis technique.  We can then evaluate our underlying assumptions against updated information and view points.  Is our approach still relevant and optimal?

We are all resistant to change and we are obdurate in sticking to what we have always believed to be true.  We resent people who challenge the assumptions behind what we have based our own perceptions upon and even create every type of excuse to hang on to our belief.  We need to be careful that most of our arguments are not just based on finding reasons to carry on with what we have always believed to be true.

All the decisions we make in life are based on our brain examining all the information we have stored related to the subject.  This information is processed by our brain and forms our perceived reality and we make a decision based on these past experiences and our logical deductions.  It is therefore natural for us to conform to the patterns that we have previously seen as these patterns form the viewpoint of our world.  The child who answered the questions has not had the experiences that most adults would have had and therefore imagined a different way of answering the question.  We need to recognise that, our perception of the world, and reality, are two completely different things.  Other people with different worldly experiences may come to different conclusions based on their individual perceptions just as this child perceived the problem completely differently to how an adult would interpret it. 

Recognising our own limitation in perceiving any problem is part of the battle.  We are not perfect and there may be other people who have different experiences.  Better deeper experiences.  We should really listen to those other viewpoints and understand how they see the problem from their perspective.  There may be a different ways to tackle a problem that we have not considered.  By recognising this, we can then free our imagination to explore different possibilities.  Truly break free from our normal conformist thinking and consider if there are other solutions.  My method for this is, after I have completely submersed myself in understanding the problem, I walk away from it.  I do something completely different like go for a run or a long walk.  Quite often the most imaginative and creative thoughts come to us when we are in these relaxed states of mind rather than when we are immersed stressing about the problem. In a relaxed mental state we can let our imagination run wild.  We can think outside the box.

The need for better leadership

Much has been written and debated about the fall of the Roman Empire: the rise of the Barbarians, over spending on the military, over reliance on slaves and corrupt leaders in the senate.  As the empire expanded, leading the empire as Roman Emperor, became a perilous job with 20 men taking the throne in the last 75 years.

As the complexity of an organisation increases we need better, more effective and stronger leadership to steer the organisation to higher levels of maturity.  These higher states require leaders to ensure the interest of all people are taken into consideration and everyone feels part of the onward journey.  We can't be in the situation where the people at the bottom of the structure feel so disillusioned that they will start rebelling against the organisation itself like the Barbarians and the slaves rebelling against Rome.

I believe this is true in all types of organisational structures such as small project structures, large companies or world governments. In a project or company environment, having a clear vision that everyone involved believes in, helps to keep everyone engaged effectively.  However, they also need inspirational leaders that will help them achieve and reach that vision.  Leaders who will make everyone want to participate in assuring the journey has a successful outcome.  This holds true at a government level too and if you look around the world today at the world leaders - how many really inspire you?  How many of the world leaders are in fact divisive and political animals looking after their own welfare?

It is my view, to make our world better in these difficult and changing times we need more effective leaders and more of them.  All organisations should make it one of their priorities to create the next generation of leaders, who are better leaders than us, so that we can continue on our onward and upward journey.

Project Planning Direction

We hear projects speak about left-to-right planning and right-to-left planning which fundamentally are different approaches based on whether we are planning for a specific end date. There may be legitimate reasons why hitting a particular date is key such as a new construction project planned in time for a particular sports event. In ERP deployments, it is rarely a good thing to plan right-to-left unless there is a burning platform that needs to be replaced. Sticking arbitrary dates in the plan is not going to drive good behaviour on the project in general.

Left-to-right planning defines a healthier direction as it makes us think, "it will take how long it take", without feeling obliged to hit arbitrary dates imposed by executives who are disconnected from the detail. This helps us to protect quality which is not a bad thing when you consider how many failed ERP deployments there are around. However, it also makes us plan in a manner of "what's first?", "then what?"

Planning in a purely chronologically sequential way is not good on either a small scale tasks or a large scale programme. How many times have you seen spreed-sheets morph into monstrosity because no one really planned what they wanted as an end result. Columns keep getting added, cells shaded, until no one can can easily understand the information being presented.

Stephen Covey told us, "Begin with the end in mind." This is particularly true for project planning where if we don't know where we are trying to get to, the road is likely to be less then optimal. Instead, we should start our project plan by laying out the key building blocks on the journey with a clear view of what good likes like on arrival. To do that we need clearly defined deliverables and we should imagine ourselves doing a deliverable review at the end of the project - what would you want everyone to say about the deliverables we produce? Now, what are the building blocks we need to complete to achieve that desired result?

Planning then becomes an iterative cycle of building out the building blocks with more and more detail - never losing sight of what the end result of each building block looks like. We can only plan for what we know so if unsure, stick a stake in the ground and keep the building block high-level until more information is known.

So planning should not be so much left-to-right or right-to-left but should be more outside-in. Get the shell of the plan locked down early and build out the inner. Never loose sight of what good looks like at the end of the project. Begin with the end in mind.