Solved: Automatically update Word Macro from Network location

[AyeSee]

Hello,

I am wondering if it is possible to have a macro automatically
1. check to see if it has the latest version or not,
2. and if it does not, automatically copy from a network location.

If the first option is difficult, is there a way to automatically do option 2?

Many thanks in advance! This question is one that if someone tells me yes I will be jumping up and down in joy....

Alex

[AyeSee]

Ok. So checking latest version is fairly simple. All you need to do is make a comparison with a number stored at any location, so that is covered. It wont run for those that dont have the proper version, and they get a popup instead telling them to have their macro updated...

But automatically update macro is still very ambiguous to me...

[fumei]

IF your flag number indicates version lag, EXPORT the code to a text file (.bas) and IMPORT it wherever you want to import it.

"But automatically update macro is still very ambiguous to me..."

I think I would change that to ambitious, rather than ambiguous. It depends on what you mean by "update". Do you mean re-writing the lines themselves? Then this is tedious.

Again, if this is important, make your procedure into its own Standard module, and use Export and Import.

[fumei]

Actually, why are you doing this? I would be MUCH better to use a global template on the network and have your working documents use that. Then you never have to update the documents. You have the one location (on the network), and any changes/updates are done there. The other documents using it are pointing to it.

[AyeSee]

IF your flag number indicates version lag, EXPORT the code to a text file (.bas) and IMPORT it wherever you want to import it.

"But automatically update macro is still very ambiguous to me..."

I think I would change that to ambitious, rather than ambiguous. It depends on what you mean by "update". Do you mean re-writing the lines themselves? Then this is tedious.

Again, if this is important, make your procedure into its own Standard module, and use Export and Import.

Sorry about that, when i meant auto-update, I mean copy the new version and replace the old version. The users use the macro to process information in a specific table. If they have an older version of the macro, it doesn't work properly because the macro is expecting a specific format. I am able to change the format from a distance, but I do not know how to replace the macro to make the update seamless.

For the moment, the code wont run if there is a mismatch. I would like for it to fetch the proper version instead. I have done this for an Access database. I imagine that the macro would work logically like this:

1. check version of macro()
2. if version out of date
a. remove old macro
b. copy macro from specified location
3. else run macro()
4. end if


The reason why I am doing this is... [although I know this seemed quite weird to you when I first explained it to you], I am performing an automated mail merge from Access using a specific Word template. I dont have a choice but to keep the macro in Normal because it creates a new iteration based on the template and does not inherit the macros associated to the template... Unless there is a way to have all the computers pointing a macro on the network? That would indeed solve all my problems

90 hours! congrats!!! That's how long there is left to my first contract :-(

[fumei]

"Unless there is a way to have all the computers pointing a macro on the network? "

Yes there is. As mentioned. A global template on the network, and a wee global in everyone's Startup. The wee one in everyone's Startup installs and loads the network one.

"I dont have a choice but to keep the macro in Normal because it creates a new iteration based on the template and does not inherit the macros associated to the template"

Actually you DO have a choice.

[Frosty]

I would say some coordination with a Network admin would be useful, and have your global templates automatically updated during a login script would be the preferred method, although Gerry's will work as well if there aren't other considerations.

Alternatively, an even "simpler" mechanism could be the following:

1. Put your global macro template on a network share.
2. Put a shortcut to the global macro template in the Word Startup folder for each user's machine (of course, they would all need access to the network share in the same way... either the shortcut is to \\mynetworkresource\mysubfolder\myglobaltemplate.dot (file://\\mynetworkresource\mysubfolder\myglobaltemplate.dot) or it is to F:\mysubfolder\myglobaltemplate.dot etc.

There are security issues with this in terms of trusted locations in Office 2007 and above.

When those users start word, they will launch the global template from the network share... HOWEVER... you will not be able to update that network template unless everyone who accesses it has closed Word or unloaded the addin (this all gets fairly complicated, but will be pretty apparent what is wrong when things break down on you).

I still prefer running things locally and having login script type things update those things when the computer is rebooted, as a general rule.

[fumei]

Actually, your "simpler" mechanism is exactly what I proposed.

global template on the network, and a wee global in everyone's Startup.
The global macro container on the network.
"Put a shortcut to the global macro template in the Word Startup folder"

Not sure what you mean by a "shortcut". For it to do anything from Startup, it has to be a template. A wee one. With one purpose - point to the real global on the network.

[Frosty]

Actually no. You can put an actual windows shortcut to a word template (i.e., Copy from network location, Paste Shortcut into Word startup path), and Word will load the network template pointed to by the shortcut on startup.

A useful little tidbit I learned a year or two ago.

[fumei]

Really? I never knew that. Startup loads actual Windows shortcuts? Gotta try that....

Wow. Now that could be very very handy. Thanks Jason! I love to learn new things.

AyeSee, so there ya go. Put a shortcut to the network global into users Startup. It loads the network global and you can maintain that ONE file.

As one of my globals (on the network) is massive, I tend to want to have things more dynamic. So I think I will keep my wee global in Startup. It does one thing - adds a icon to a toolbar. This points to a toggling procedure. If my massive network global is NOT loaded, it loads it. If it IS loaded, it unloads it.

Remember global add-ins can be dynamic. But if they are loaded via Startup they are NOT dynamic. Unless of course you write an additional procedure to get rid of them.

[Frosty]

I know, it's a crazy little "feature" that I don't think is documented.

But it definitely has limitations, especially in a multi-user environment. I'm still a big fan of always running things locally, and using login scripts to download at bootup, at least as a general rule.

What do you mean when you say global add-ins can be dynamic, but loaded via startup they are not dynamic?

[fumei]

Well you can add and remove them dynamically, as required. In other words, you can add one somewhere down the line, whenever you want it.

A global (or a pointer via shortcut) in Startup always loads on Startup. Whether you are using it for that document, or not.

Yes, they are still dynamic in that you can get rid of them, but they are not dynamic in that you have no control of their initial loading. If they are in Startup they ARE loaded on Startup.

OK, OK, a bit fussing on the word "dynamic".

Depending on circumstances the Startup global can load/unload (as well as install/uninstall) network globals as appropriate. Like load the Financial network globals (but not the Personnel). Or whatever.

[Frosty]

Ahh, now I understand what you meant. Totally makes sense. Thanks. Sorry to hijack original thread!

[fumei]

It is - sort of - relevant.

[AyeSee]

Wow, I learned quite a few tricks with this one. The only thing though is does creating a network global allow for a macro within it be accessible for any word file? That is what I need based on my current configuration.

For future projects I will definetly use this approach.

And how would i have the option to use Normal if when i do a mail merge on a template, the macro does not follow on the new iteration?

[Frosty]

Just as macros in the normal template are available to documents NOT based on normal... the same is true of any macros in any global addins.

If a global addin is loaded (which it generally will be, if it or a windows shortcut pointing to it is in the Word Startup folder) the macros in that project will be available while in any document.

So macros in:
Normal.dotm
GlobalAddin.dotm

are available in documents based on Normal, as well as documents based on Letter.docx.

Hope that helps.

[AyeSee]

O.k.

The fact that you are saying that with such assurance brings my hopes up. So how would I go about setting up the GlobalAdmin.dotm? This is the procedure I have tried that does not seem to work:

1. upload the macro on a template.
2. Save template on a network location.
3. Put shortcut to template in startup folder.

-- This is where it does not work, but what I expected to happen based on my understanding of your suggested procedure
4. Customise toolbar to add macro to toolbar
5. Find macro from GlobalTemplate (does not seem to be found in the marcros)


Many thanks in advance
Alex

[Frosty]

I would back up a step. First... point your word startup folder to the network folder your global addin is in.

Try to find your macro... if you can't find it, then you've got some macro security issues to sort out.

Then, move the startup path to a local location, and put the windows shortcut to the global template there.

You can put the user-interface stuff to the macro within the global template too, but that's really up to you.

[AyeSee]

Thanks for your response Frosty

I just need a couple of precisions to be sure we are talking about the same thing.

"word startup folder"

Do you mean the windows startup folder? As in Start --> Programs --> Startup ?

"Global Addin"

What is the Global Addin? Where can I find this? Is this a .dot file that is on a network drive?

Thanks infinitely,

[Frosty]

What version of Word are you working on?

Word2003, word startup path is
Tools > Options > File Locations > Word Startup

Word 2007
Office Button > Options > Advanced > File Locations > Word Startup

Word 2010
File > Options > Advanced > File Locations > Word Startup

GlobalAddin.dot (or .dotm) is a made up name. It is whatever the document is that you have put you code in, and then saved as a template, and then saved to the word startup path.

Once you get that worked out... you can move the template to a network location, and then put a windows shortcut to that template in the previous location.

At that point, you will have to make sure that your macro security, also located in the Options area (depending on your word version).

You may need to talk with your network administrator about security settings, as that may be an issue for your organization.

Hope that helps.

[AyeSee]

Wow.

Mind = blown.

I figured what you meant by Word startup folder... its in the user files...

Thanks!

[Frosty]

Well, it's where ever you want it to be... by default it is in the user's profile. You can move it wherever you want. But again, there are many considerations to take into account if you are setting up this kind of structure.

In essence you are trying to come up with a deployment strategy for your macro. At the point at which you are distributing your macros to other people, I think you should probably keep investigating and talking with some IT professionals about what is best for your organization, rather than simply implementing some advice you got for free in a forum.

I'm not soliciting business for myself, I'm plenty busy... just trying to give some friendly advice.

[fumei]

I would agree. Not sure of your position, but if you want to do some sort of scripting to make sure each user gets a file in their Startup (Word Startup, not Windows Startup), then for political considerations, you may want to make sure your IT people are on board.

[AyeSee]

Yes,

To give you a picture of my situation, I have been working as a summer student. I was doing a mandate for a business unit, but I am not structurally linked to IT. Therefore I am technically not following standard procedure, which is why I am stuck to following advice on forums.

That being said, I am at the final stages of the mandate and am now taking care of giving IT the required tools to give minimal support of the tool (installation). Ensuring that IT support can truly support my tool afterwards is my current priority.

Another thing about the tool is that it's a tactical tool (intermediate) preceding the implementation of SAP CLM (contract lifecycle management). The most important part of the summer project was getting the process standardised. From a technical standpoint, this database only affected 8 people. It does not really matter if this tool is kept on the side given a bigger more expensive project is coming; the current database was to solve a critical problem that could not wait the arrival of the SAP module. Quite certainly, I understand the importance of implicating central IT, but to keep things simple, cheap and quick, thigns were kept simple and documentation will be delivered before I leave.

Thank you for the advice though, it is true that IT will not be happy if I just dump it the day before I leave. I will be meeting with them soon.

[Frosty]

Well, this is a pretty rinky-dinky way to solve what is, essentially, an issue of deployment. I don't know how much time you have to come up with another alternative, but I would gently suggest investigating the use of a logon script or group policy stuff to do your deployment. That tends to be robust, and you can check in with ActiveDirectory group membership to determine who gets what stuff deployed to them.

The problem with this solution is that the windows shortcut will get wiped out in various scenarios (generally when helpdesk is troubleshooting other Word issues)... so it's really not an ideal deployment strategy, even if you get all of the moving parts totally laid out clearly-- someone will still screw it up.

Having your solution getting updated after a restart is going to be easier for everyone involved. So getting your deployment hooked into your existing IT structure is going to serve you much better.

Where I've used the above windows shortcut scenario is for particular end-users having custom code they want to have preserved without fear that Help Desk is going to wipe out their custom macros when troubleshooting word issues.

As a suggestion... I'm by no means a heavy logon script guy, but what I've done has used kixtart.org stuff, which is a more robust scripting language than I've ever needed, but learning curve isn't steep at all.

From a pure project management perspective, typically your network engineers/system administrators are the ones that need to ultimately be deciding about the deployment strategy. As an inhouse developer you just need to come up with the requirements (i.e., I need these files there, these dlls registered, etc) and maybe recommendations.

But a system which is entirely outside of your ActiveDirectory (or whatever authentication process you have) is probably going to cause headaches down the road.

Just some additional thoughts.

[AyeSee]

Even if it's just two shortcuts that need to be copy pasted?

Thanks to advice from several sources including yours, the installation process is copying and pasting two shortcuts on the system... 1 for the database, 1 for the macro...

But I understand what you mean, I will go right now to set up a meeting with IT to make sure what is best.