Autolink between MSP03 & Excel03

[Junkyard Engineer]

I have a projects dashboard built within Excel.

Our projects are managed with MSP03. In each of them, I added a text column
named Risk Level. A code between 1 and 9 is typed in by the tasks managers.

What I want to do is to import or export depending from where you stand
those risk levels within Excel so the conditional formatting (green, yellow,
red) can be applied so the boss knows what projects and details are at risk.

The thing is that I want Excel to automatically fetch the appropriate data
within all the MSP projects at spreasheet startup.

What do you suggest ?

 

[Junkyard Engineer]

Re-reading the question, it looks a lot like a Paste-Link application. I
tried it and it seems to work.

 

[John]

Re-reading the question, it looks a lot like a Paste-Link application. I
tried it and it seems to work.

Junkyard Engineer,
For your simple link of data a Paste Link is one option, however, paste
links are very fragile and subject to corruption. For them to work
properly they need to be managed very carefully using a lot of
discipline. For example, one of the areas of most trouble with paste
link information is when the user changes the location of either linked
file - the link gets lost. Another problem is in breaking the links.
Even though only one "side" of the link is visible (via Edit/Links) in
the destination file, link information actually resides in both files.
Not properly saving both files after a link is broken will result in
link fragments that can cause corruption (been there, done that).

In my opinion VBA is a better approach, but if VBA is not part of your
experience base then Paste Links can work if used with caution.

Hope this helps.
John
Project MVP

 

[Brian K - Project MVP]

To add to this make sure that you NEVER (EVER!) use Cut\Paste to move a
task if you are using links. It will kill your links between project and
excel. FYI

 

[Junkyard Engineer]

Hummm... well sounded advice guys, thanks.

Then paste-link option is good enough for the technology showcase. But then,
I'll have to secure that part a lot more before implementation in the
office. KISS is the word here

VBA is not really my forte but I made part of my studies at university in
programming system (all languages) 20 years ago. I've played a little with
vba in excel so we the good instruction set in both XLS & MPP I should get
me around pretty quickly (I hope).

So where do I start ? VBA for dummies ? Or is there already something close
done somewhere in the MS database ? From my little experience in XLS it's
the instruction set list that is the most difficult to get. The programming
syntax has always been the same as far as I can see.

tia

 

[G Lykos]

John, please expand on your comment: "Not properly saving both files after
a link is broken will result in link fragments that can cause corruption."
More specifically, what do you mean by properly saving both files? And how
correctable/fatal was the corruption once occurred in your experience?

Thanks,
George

 

[John]

Hummm... well sounded advice guys, thanks.

Then paste-link option is good enough for the technology showcase. But then,
I'll have to secure that part a lot more before implementation in the
office. KISS is the word here

VBA is not really my forte but I made part of my studies at university in
programming system (all languages) 20 years ago. I've played a little with
vba in excel so we the good instruction set in both XLS & MPP I should get
me around pretty quickly (I hope).

So where do I start ? VBA for dummies ? Or is there already something close
done somewhere in the MS database ? From my little experience in XLS it's
the instruction set list that is the most difficult to get. The programming
syntax has always been the same as far as I can see.

tia

Junkyard,
Are you part of "Junkyard Wars" or something (i.e. Junkyard Engineer)?
Oh I get it, you're in the sanitation industry and everybody is an
engineer these days (e.g. "domestic engineer")

Ok, enough of my sad humor. I applaud you for being willing to pick up
where you left off 20 years ago, so to speak, and opt for learning VBA.
There is an excellent tutorial for that on our MVP website. Go to:
http://www.mvps.org/project/links.htm
and look at the link near the bottom of the page, "Project 98 Visual
Basic Environment Training Materials". Although it says it is for
Project 98 it is equally applicable to all current versions of Project.
The only difference is that Microsoft generally expands the Project
object library with each new release. However, for what you want to do,
the basic library will give all the tools you need.

In addition, I recommend fellow MVP, Jack Dahlgren's website that is
loaded with VBA examples (including on that shows an interface between
Project and Excel - Export Hierarchy to Excel). Jack's website is at:
http://masamiki.com/project/macros.htm.

Last but not least are the resources available in the newsgroup. Many of
us have extensive experience with VBA.

Hope this helps.
John
Project MVP

 

[John]

John, please expand on your comment: "Not properly saving both files after
a link is broken will result in link fragments that can cause corruption."
More specifically, what do you mean by properly saving both files? And how
correctable/fatal was the corruption once occurred in your experience?

Thanks,
George

George,
It has been several years since I worked with Paste Links on a daily
basis. With Project version 4.x it was the only option available for
externally linking tasks between projects so I gained a lot of "battle
worn" experience. As far as I know, the basic structure of Paste Links
is no different in current versions Project than in that early version.

As I recall, I had the most success when breaking Paste Links if the
source file was saved first (after the break) and then the destination
file. If that sequence wasn't used, I found link fragments remained and
eventually caused corruption. It wasn't necessarily fatal and generally
would go unnoticed for some time but eventually files would grow in size
(i.e. bloat), exhibit strange behavior, or sometimes crash or fail to
open.

Before I knew anything (sometimes I still don't) I tried breaking links
by simply deleting the destination task. Project will pop up a message
saying the task has links. However after finding out that was not a
smart approach, I learned the best, and perhaps only good, way to break
Paste Links is via Edit/Links and using the "break link" button. That of
course should be followed by the save sequence outlined above.

So as I say, if the user is very disciplined and diligent, Paste Links
can work. Unfortunately in my experience, most users are probably not
that disciplined or diligent and, with current versions of Project there
are much better ways to link information between Project files or to
other applications.

Hope this helps.
John
Project MVP

 

[Junkyard Engineer]

Don't worry, I think the same about a lot of so called engineers

I'm actually a Polytechnic graduate so a "real" engineer and the nickname
comes from working with small businesses without cash to do things the right
way the first time so I had to do some Junkyard Wars tricks.

Thanks for the links ! I'll dig into that !

 

[John]

Don't worry, I think the same about a lot of so called engineers

I'm actually a Polytechnic graduate so a "real" engineer and the nickname
comes from working with small businesses without cash to do things the right
way the first time so I had to do some Junkyard Wars tricks.

Thanks for the links ! I'll dig into that !

Junkyard,
Go ahead, name me one company large or small that is WILLING to invest
the cash to "do things the right way". That's why they have a few clever
employees (not necessarily real engineers) to figure out how to get
something done in unconventional ways.

By the way, "doing things the RIGHT way" is a political pandora's box.
It is always complex because there are many "right" ways.

John

 

[Junkyard Engineer]

hey hey, you look like somebody who has few gray hairs like me

 

[G Lykos]

John, what are alternative methods to "paste link" that are available in
current Project that you allude to below?

Thanks,
George

 

[John]

John, what are alternative methods to "paste link" that are available in
current Project that you allude to below?

Thanks,
George

George,
In my opinion, VBA is the way to go. It is more reliable, not prone to
corruption and has total flexibility. But, VBA isn't for everybody.

If I worked with SQL I might be tempted to say that is another approach,
but then that is only supposition on my part since my knowledge in that
area is essentially zero.

John