Xltm opens the original not a copy

I'm a little bemused by the process you have described. When you save an Excel file as a template, you subsequently open it by selecting New and navigating to where you have saved/stored the template. After you have used it the first time, you should be able to see it in your personal list of templates. You don't need to navigate to the folder. The only time you double click on the file itself is when you want to edit it.

If you are double clicking on the template, you are opening it as a normal Excel file.

Regards, TMS

If it is intended to be a template, you save it as a template with an appropriate file extension in a template folder. You NEVER double click on the original file UNLESS you want to edit it. And it should, ideally, only be edited by the owner. YOU, by the sound of it. Other users should NEVER navigate to the folder and they should NEVER double click on it ... otherwise it will be opened in edit mode with full access ... it will be treated as a normal file. I can't say that in a different way.

Alternatively, you can save the original "template" as read only. You will then be prevented from over-writing it.

It won't be in THEIR template folder, it will be where you save it. They would have a "pointer" to it in their LIST of available templates.

If they double click on it to open it, it is not being used as a template, it is being used as a "base" file which they should use Save As to create a new copy. There is, though, a risk that it can be changed.

No, they can function as templates wherever you want to save them. But you need to open them using NEW, not OPEN. Otherwise, they are not, strictly speaking, being opened as templates.

For example, Book is the built in template and when you select New and double click on Blank Workbook, you get Book1, Book2, Book3, etc.

Maybe this will explain better how to use and create templates: http://www.excel-easy.com/basics/templates.html

Hi Trevor,

Thanks for that link. I'm wondering--is a pointer the same as a shortcut? If so, I'll put shortcuts to the network files in the users template folder. Then it will update when I make changes; she won't need it on her desktop; she'll pick it from the excel file menu--right?

I'll need to do that tonight after she's stopped working.

Then I'll check the results tomorrow.

Thanks again,

Dan

Shortcuts allow you to have references in different places, all pointing to the same file. It's used for convenience, to save having multiple copies of the same file. So, for an executable file, like Word, or Excel, or internet Explorer, you have one copy of the application that Microsoft updates, but lots of shortcuts/pointers to it ... on the desktop, on the Start Menu, on the Task Bar, and so on. So, you can have shortcuts to excel spreadsheets but that's not quite the same here. Don't put shortcuts in the templates folder.

Clearly, I am not explaining this well. If you save your workbook as a template on the network somewhere, your user(s) can create a NEW workbook using it. The first time they want to create a NEW workbook based on your Template, they will need to click on "New from existing ..." and navigate to the folder where you have saved the template. They will select the template and click on "Create New".

In the example below, I have saved a template on my "G:\" drive which is on another computer on my network. The first time I wanted to use it, I needed to navigate to the G:\ drive and go to the folder with the template in it.

Once it has been used, it will appear in the section "Recently Used Templates" and, if you hover the mouse/cursor over the icon, it will show the location and file name of the template. You can either click once and Click on "Create" or double click the icon.

Saving the template as Read Only is probably still a good option because there is NOTHING stopping the user(s) clicking on OPEN and navigating to the template and opening it as an ordinary file. I suspect that, if the normal process is to open the template (as a normal file) and immediately save it with a different name, you would never be aware that this is what they did. It is possibly a timing issue in that someone opened the file as an ordinary Excel file and were making their changes on your original template before saving it. That would be my guess anyway.

I hope the images below help you to understand what I have been saying.

Regards, TMS

New Workbook From Template.jpg

New Workbook From Template v2.jpg

Thanks for the rep.

The file should reside the the user's template folder

No, you would have a single, central copy of the template somewhere on your network where everyone has access to it. You could, and should, make it read-only. To edit it, you would remove the read-only attribute, edit the template, save it (back over itself), and then re-instate the read-only attribute. It would be a good idea, and good practice, to make a copy of the template before you edit it.

This way, you have a central copy of the template which only you will edit. The users don't really need to be aware of the fact that you have changed the template. It would, however, make sense to have a cell somewhere that indicates when the template was last updated ... and maybe even a change control that summarises the changes that have been made.

As I said, once the template has been found, it will appear in the Recently Used Templates area and, effectively, you/they will have a pointer to the master copy on the network.

If I'm honest, I don't know how double clicking on a file will ever open a copy of a workbook. And, equally, without access to the individuals' computers to compare the way they are set up, I have no idea why one machine would behave differently. I think I will need to seek advice.


Regards, TMS

It just occurred to me that you save the template as a macro enabled template. So, what does/do the macro/s do? If it has a Workbook Open event handler, that could, perhaps, save the workbook locally. That might explain why it reacts differently for one user. Maybe, for some reason, the template workbook cannot be saved locally. As a consequence, the user would continue working with the live template workbook.

Has the user deleted or renamed any folder(s) on their machine?

Can you share the macros that the template uses? Could you share a copy of the template, with any sensitive costing information removed?

When I doubleclick in Explorer the file opens as a copy (VBA workbooks.add "G:\OF\example.xltm")
When I doubleclick in Excel, OpenFile it opens the file itself (VBA: workbooks.open "G:\OF\example.xltm")

@snb: Excellent. Thank you for that insight! Shows how often I double click on a template in Windows Explorer.

My assumption, throughout this discussion, has been that the user has been double clicking in Excel; never even considered double clicking in Windows Explorer. Perhaps that explains the difference? Maybe the one user has discovered that she can double click on the template in Excel!

Regards, TMS

I give the users a shortcut, so they don't normally navigate to the network drive.

This hasn't been solved yet. What does (s)he give to the users: are we talking about .lnk files ?, a hyperlink in a file ? , etc.

In XP, the default double-click behavior for a template was easy to set as either New or Open, right in the Folders window. In W7, that selection has been buried in the registry. I have the behavior I want (New), and so have never followed the path to find its home, but the OP may need to.

In the XP register I find this here:

HKEY_CLASSES_ROOT\Excel.TemplateMacroEnabled\shell\New\command

Command

"F:\MS-Office-10\Office14\EXCEL.EXE" /dde /n

The switch /n creates the new file based on the template.

With Windows 7 64 bit and Excel 2007, I have:

Command
(Default): "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /e /n
command: vUpAVX!!!!!!!!!MKKSkEXCELFiles>tW{~$4Q]c@II=l2xaTO5 /e /n

I think the second one may be something to do with multiple instances of Excel ... but not sure.

And there are also similar keys under Open but with the /n removed.