Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

How to create an EXCEL GANTT CHART using VBA

17 replies [Last post]
Mohamed Ghafir
User offline. Last seen 41 weeks 4 days ago. Offline
Joined: 29 Sep 2017
Posts: 5
Groups: None

I'm a planner in the UK, and been working as a planner for just over 2 years in few different rail projects.

and one of the main challenges is that most of the engineers were NOT reading the P6 PDF programmes, 
even if they did, they really didn't pay attention to the details.

because everytime I talk with the engineers to get updates on their activity, 
them sound like they haven't read the programmes and don't know the dates that they should be aiming for,

and eventually one of them told me it's because the programme is too big,
there's too much text. 
too many columns.

the Ghantt Chart looks complicated because the activities are not sorted in order, 
even if we sorted by finish date the problem wasn't really solved
because there's a feedback loop between multiple disciplines, 
so they will have to go up and down from page to page to know what's the predecessor/successor of a certain activity. 

Doing filters in P6 can diffinately help, but it doesn't solve the problem completely, 
it still requires searching back and forth to follow a sequence of activities.

So after a discussion I found out they don't care to read the programme, 
They ONLY want to know what are the activities they need to focus on in the next 1,2,3 or 4 weeks.

That's IT!

I knew excel may have the solution, because you can easily
highlight a set of columns and look at the activities that lie in the shaded area.

so I searched google for Excel ghannt charts, and I found a bunch of templates,

I used many of them, but non offered anything better than what we have in P6
This is when I discovered Excel VBA,

I started dabbling with it and saw the huge power in this software,
basically, what you can do is limitless! HOWEVER.

It required to either hire a Developer to create a VBA code and template for you,

or learn how to code and do it myself.
I choose to do it myself and took multiple courses on the subject just to make this idea work.

After alot of trial and error and help from some really talented developers, 
I finally got the software that has all the features I want and MORE.

**With a click of a button, it can do all of the following

1) as you can see above to create kind of a visual horizontal sequence of activities on excel (instead of 1 activity on each row)
2) It Automatically COLORS activities based on their status

Completed = Blue
Green = Not Started & Not Critical
Orange = In-progress & Not Critical
Red = Critical activity (Not Started or In-progress)
Pink = Near Critical (you can define the near critical)

3) It positions the activity start and finish date (it's very accurate)
4) You can change the dates columns to be daily or weekly as show below

  • 1 day per column,
  • or 1 week per column (see below)

 

Since using this, all engineers have been looking and engaging with the programme at every meeting (Finally)!

I'm planning to give this for free to the planning community,

PS: there are extra features (really good & time-saving extra features) that I will talk about later, if I see some interest.

If this is something you are interested in, please let me know and I will give you the free version that I talked about above and I will be very happy to know I made a contribution.

Otherwise I'll be the only user of this tool

Replies

chris cho
User offline. Last seen 8 weeks 6 days ago. Offline
Joined: 9 Sep 2019
Posts: 4
Groups: None

The previous link to Youtube was directed to incorrect video.

You can visit the link below and download the program.

https://www.youtube.com/watch?v=oCWJg_SwOWc

Out-of-sequence is sorted out in the program.

Romie Isanan
User offline. Last seen 9 weeks 2 days ago. Offline

Hi sir can you share it to me romie.matiere@yahoo.com

Romie Isanan
User offline. Last seen 9 weeks 2 days ago. Offline
Hi sir can you share it to me romie.matiere@yahoo.com
Romie Isanan
User offline. Last seen 9 weeks 2 days ago. Offline

Hi sir can you share it to me romie.matiere@yahoo.com

Romie Isanan
User offline. Last seen 9 weeks 2 days ago. Offline

Hi sir can you share it to me romie.matiere@yahoo.com

Maha Adji
User offline. Last seen 10 weeks 4 days ago. Offline
Joined: 28 Jan 2009
Posts: 7
Groups: None

Dear ed Ghafir,

Would you please send me the program or link to my email adress : m50adj@gmail.com

I'll appreciate.

 

Engel Espinoza
User offline. Last seen 10 weeks 4 days ago. Offline
Joined: 23 Oct 2019
Posts: 4
Groups: None

Hi Mohamed, I would like to try your program too, could you share me a copy? my email is: engel.espinozac@gmail.com

Thank you in advance

Engel Espinoza
User offline. Last seen 10 weeks 4 days ago. Offline
Joined: 23 Oct 2019
Posts: 4
Groups: None

Hi Mohamed, I would like to try your program too, could you share me a copy? my email is: engel.espinozac@gmail.com

Thank you in advance

chris cho
User offline. Last seen 8 weeks 6 days ago. Offline
Joined: 9 Sep 2019
Posts: 4
Groups: None

You may well visit below and download the program for your use

https://youtu.be/mW1uj-026xA

Bian Mutang Tagal
User offline. Last seen 2 weeks 4 days ago. Offline
Joined: 12 Jun 2014
Posts: 17

-deleted-

Bian Mutang Tagal
User offline. Last seen 2 weeks 4 days ago. Offline
Joined: 12 Jun 2014
Posts: 17

 Hi Mohamed Ghafir, it would be nice to have a go at your program. A few years ago I developed an Excel VBA program AutoGrouper that groups and formats hierarchical WBS data extracted via copy-paste from Primavera P6. Essentially with just one click the raw data pasted to Excel would be transformed to the familliar default Activity Table look (blue, red, yellow, activity level, etc). The next step was to show the corresponding Gantt Chart but I never got round to figuring out how to do it.

It would be nice to see how we could integrate our code. I'm not a programmer by trade but I did try to adhere to proper programming practice such as short line length, CamelType, OOP concepts, error-catching, and using MZ-Tools. The only problem is I need some time remembering the password to my code, as this was before I started using password managers >.<

Will PM you my email address

Albert Teng
User offline. Last seen 9 weeks 19 hours ago. Offline
Joined: 10 Jun 2020
Posts: 2
Groups: None

Hi Mohamed Ghafir,

Would appreciate if you could share the program or send link to my email, albertt128@gmail.com. Thank you in advance.

ANIL VARGHESE
User offline. Last seen 35 weeks 6 days ago. Offline
Joined: 19 Mar 2020
Posts: 2
Groups: None

Dear Mohammed,

Could you please send me this  program to my e-mail adress anilvargheseg@gmail.com

Bui Duc Thuan
User offline. Last seen 18 weeks 6 days ago. Offline
Joined: 9 Jul 2013
Posts: 2
Groups: None
Dear Mohamed Ghafir, May you share with me a copy? Bdthuan@atcdss.com Thank you Thuan
hafiz abidin
User offline. Last seen 2 weeks 4 days ago. Offline
Joined: 24 Jun 2016
Posts: 2

Hi Mohamed Ghafir,

 

I would like  to try this program, can you share this progrom along with instructions. Kindly email to me at apihbaik@gmail.com

Christopher Middl...
User offline. Last seen 37 weeks 4 days ago. Offline
Joined: 24 Apr 2019
Posts: 2
Groups: None
Hi Mohamed I have sent a message with regards to receiving this via my email address as outlined in the message. Your work is greatly appreciated.
Zoltan Palffy
User offline. Last seen 6 hours 28 min ago. Offline
Joined: 13 Jul 2009
Posts: 2812
Groups: None

Mohamed

Since you are planning to give this for free to the planning community

I would like to try this program. Can you please send this to me along with any instructions.

If you want I can help to promote this program.

I have sent you a message here on Planning Planet with my eamil address