Solved: What is best way to import the latest version of a modules on to user's PCs?

[hkeiner]

What is the best way to make sure that the latest version of some custom macros (userforms/modules) that I periodically revise are always automatically updated to each user's PC in my work group? I want to do this without the need to get the PC users involved each time the UF/Ms are revised or to physically access the PCs directly to do it myself.

I came up with a process that works OK but I am hoping that there is a better way. Currently, I use an AutoOpen macro on each user's Normal Template that automatically imports certain BAS and FRM files from a shared server. Some of the custom UF/Ms are placed on the Normal Template and some on the Active Document. To avoid the need to have the autoexec code determine if a newer version of the UF/Ms exists on the server (and to avoid duplicate imports), I simply have the autoexec macro 'remove' the existing custom userforms/modules and then "import" the same custom UF/Ms again (via BAS and FRM files) from the server each time the Word document is opened. This way, the custom UF/Ms that are available to users when a Word document is open matches the latest version that I maintain in a directory on a shared file server. So far, so good.

One big concern that I have is that I currently import several custom userform/modules on to the Normal Template of each user's computer. I do this becasue I created some QAT icons on each user's PC and mapped them to some of the subs on the Normal Template. I have read that using the Normal Template for this type of purpose is not very good practice. I understand why, but I don't know a good alternative. I have only been dabbling with VBA for Word 2007 for a few weeks, so I am not very aware of what else is possible.

I have tried to use a global template (instead of the Normal Template) but I can not import BAS/FRM files to it using VBA code. It is locked when trying to work with it in the VBA editor. Also, I am not allowed to change the template used for creating these new Word documents because the Word documents are created by a document management system using a special template that I can not (should not) touch.

Any thoughts or suggestions would be appreciated.

[fumei]

"I have read that using the Normal Template for this type of purpose is not very good practice. I understand why, but I don't know a good alternative. "

The global template is the alternative.

"I have tried to use a global template (instead of the Normal Template) but I can not import BAS/FRM files to it using VBA code. It is locked when trying to work with it in the VBA editor."

That is because the template file itself must be open. So, open it.

Other than that, yes, importing Bas and FRM files is the best way. Although, depending on how things are set up, a one stop update of a global itself may be better.

[hkeiner]

a one stop update of a global itself may be better



I played around with the two alternatives, (1) automatically import new BAS/FRM files from a shared server to the Normal Template when the applicable documents are opened vs. (2) using a common template file located on a shared server. Your suggestion to use a shared (global) template worked much better for me.

The "automatically import BAS/FRM" approach required a lot of extra code and error control so that it would correctly replace (delete old & import new) the forms and modules automatically on the users' normal templates. The "shared template" approach didn't need any such code. I just make changes to the shared template when needed, and the changes go in to effect the next time a user runs a QAT mapped to a sub on the shared template. Works great and is much more benign to the users' computers and does not require changes the users' normal templates at all.

Thanks

[fumei]

I just make changes to the shared template when needed, and the changes go in to effect the next time a user runs a QAT mapped to a sub on the shared template. Works great and is much more benign to the users' computers and does not require changes the users' normal templates at all.

Oh yeah. MUCH more benign. This is basically the whole point of gloabl templates. Especially in a network environment. It keeps maintainence to a single one-stop location.

Further to this, globals (properly done) can be dynamic. That is, they can be loaded and unloaded, at will.

What I have is a wee global scripted to be copied into everyone's Startup. Startup globals are ALWAYS loaded.

This wee global does one thing, and one thing only. It puts a menu item into the GUI. This lists:

Financial
Personnel
Letters

(whatever)

Clicking on one of the items loads another global that is on a network drive.

Thus clicking "Financial" brings in (and loads) U:\templates\Financial.dot, which has all the procedures and stuff for Financial situations.

Thus clicking "Personnel" brings in (and loads) U:\templates\Personnel.dot, which has all the procedures and stuff for Personnel situations.

etc.

That way everyone can get the template they need, but they do not automatically get everything.

Further, I have them toggled. Clicking again, removes the global. I do not like clutter, and I do not like having things sucking resources unless I need them.

Agan, this is unlike globals in Startup which remain loaded for the entire Word session.

AND, if I need to edit things in just the Financial procedures, I can edit JUST the financial procedures in Financial.dot.

Remember, global templates are essentially code containers. They are buckets of procedures. They do NOT have any effect on styles.

[hkeiner]

The "dynamic" approach you describe above seems like something I may want to try too. Would this work with Word 2007, and if so can you provide a little more detail info on how to do this, along with perhaps some example VBA code. I am pretty new to using VBA but I have read a couple of VBA books to learn some of the basics. However, one does not easily learn many special approaches, such as the one you describe above, from these books. Your advice has been gold...

Thanks

[fumei]

I will give a abbreviated simple example.

1. I have a .dot file - LoadVBA.dot - which is scripted to be downloaded into each users Startup. Actually, the script tests to see if it is there yet. If it is there, the script terminates. If it is not there, the .dot file is downloaded.

2. LoadVBA.dot has ONE procedure. This is it.
Sub LoadVBA_DOT()
Dim myAddin As AddIn
For Each myAddin In AddIns
If myAddin.Name = "VBA XTools.dot" Then
AddIns("X:\Gerry\MyTemplates\VBA XTools.dot").Installed = False
AddIns("X:\Gerry\MyTemplates\VBA XTools.dot").Delete
GoTo MyEnd:
End If
Next
AddIns.Add "X:\Gerry\MyTemplates\VBA XTools.dot", Install:=True
MyEnd:
End Sub
It also adds ONE icon to a toolbar. The icon fires the procedure LoadVBA_DOT.

That is it. That is all the global does. But what is that?

It loops through the Add-ins collection. If one of them is VBA XTools, it uninstalls it, and then deletes it. (They are NOT the same!).

If VBA XTools is not in the Add-ins, it installs VBA XTools.

In other words, a toggle. If it is not there, put it there. If it is there, remove it.

The added add-in - "X:\Gerry\MyTemplates\VBA XTools.dot" - is on a network drive. Thus every one can get it, and it is maintained as ONE file.

In this case VBA XTools is a code container (global template) with 400+ procedures.

In the example I mentioned before, you could have the wee global (in Startup) have a menu item, with sub-menus. each sub-menu could point to the installation/removal of its own global on a network drive.

[hkeiner]

It also adds ONE icon to a toolbar.


Does the above apply to Word 2007 users too? Word 2007 has a Quick Access Toolbar (QAT) and a Ribbon, but no 'toolbar" like existed in earlier versions of Word. If it does apply, would the icon appear in the QAT or Ribbon? I thought I would ask before I spend lots of time trying to get this to work with my Word 2007 users. As a newbie, it often takes me lots of extra time trying to figure things out.

Thanks

[fumei]

I do not use 2007 (hate it), but I would say that it really does not matter. I know you can modify things, and however that is done, that is what is done.

The issue vis-avis globals remains the same. They are code containers. HOW they get displayed, be it icons on a toolbar, menu on a toolbar, or icon on QAT - that is version specific.

The LoadVBA_DOT procedure does not do anything with display (menu, icon whatever). It is simply a test of the Add-ins collection.

[hkeiner]

Thanks for the additional information. We are 'stuck" with using Word 2007 at this time so I have to work within it's limitations/features regarding automatic customizing of each users' QAT and/or Ribbon.

Figuring out how to automatically add/remove icons on the QAT using VBA code has been problematic for me so far. Doing this manually on each user's PC is not a problem, but doing this as part of VBA code (i.e., adding/removing an icon based upon the conditions of the opened document) is a problem. I posted the below linked thread asking about this particular subject, but so far no bites.

Thanks again..

http://www.vbaexpress.com/forum/showthread.php?t=34907

[fumei]

Yes, I noticed that one. I can not help. I am a little surprised that there are no responses.

How about making it a keyboard shortcut? AFAIK, these function the same in 2007.

[hkeiner]

The way I have it set up now, each user has some QAT icons (mapped to macros on the global template we have been discussing earlier) which do what they should do if the correct type of Word document is active and do nothing if the wrong Word document is active. This prevents the user from accidently using the custom QAT macro on the wrong Word documents. This all works fine now.

I just wanted to add a little 'visual elegance and polish" to what I have done by displaying the custom macro QAT icons only when a correct Word document is active/opened and hiding/removing the QAT icons when any other Word document is active/opened. Not important to functionality, just aesthetics....

Thanks again for all your help, especially on the global template stuff.