Excel Template Trouble

  pc moron 12:02 03 Nov 03
Locked

I'm using Excel 2002 on Win xp.

If I edit and save the template (by re-setting the macro paths) and then re-open it everything works okay, all the macros work as expected and are pointing to the current open workbook- which is what you'd expect.

If I now close Excel and then re-open the template none of the macros will work, the path to each macro has been altered so that Excel now looks on the hard drive for the macros rather than in the open workbook. The filename it looks for is the filename of the open workbook.

I'm obviously doing something wrong but I can't figure out exactly what.

  VoG II 12:54 03 Nov 03

Try saving the template in the default templates folder. I'm not sure where this is (not at an XP machine at the moment) but if you do a search for *.XLT you should find it.

  pc moron 20:41 03 Nov 03

I've not had to deal with templates in Excel 2002 until now.

As far as I can tell from Excel's Help feature the template is in the correct location, ie:
C:\Documents and Settings\User_name\Application Data\Microsoft\Templates.

If I load the template and then save a workbook based on the template it works okay. I can open and close the workbook any number of times and the macros do what they're supposed to.

If I load the template itself, straight after editing the macro paths, it all works okay. It's only when I close the workbook and then re-open the template that the error occurs as follows:

TemplateName1.xls could not be found. Check the spelling of the file name, and verify that the file location is correct etc, etc.

TemplateName1.xls is a workbook, not a template. Templates all have an .xlt extension.

When you save a template you need to ensure that you have selected Save As and then at the bottom of the resulting dialogue box ensure that you have selected the type Template (.xlt) from the drop down list under the side heading of Save as type...

HTH

  pc moron 23:00 03 Nov 03

Thanks for the response.

The template was saved as you describe.

I open a template using Start> New Office Document> General Tab and select a template.

When the template loads the workbook name displayed is TemplateName1.xls, which is what you'd expect.

If I now click a button in the workbook I get the above error message, it seems Excel is looking for the macro on the hard disk rather then in the current open workbook, where the macros should be.

If I edit the template to correct the paths, save it, and re-open it, it all works okay.
If I exit Excel (workbook not saved) and then open the template again I get the error until I again edit the template.

  VoG II 23:07 03 Nov 03

No. Whisperer is correct - it should be saved as .xlt

  pc moron 23:13 03 Nov 03

The template was saved as .xlt.

I have done a simple trial and cannot replicate your problem.

I opened a blank Excel workbook and immediately saved it as a template file TrialMacro.xlt

I then inserted a couple of macros and again saved the workbook which obviously saved as the TrialMacro.xlt.

I then closed the file and reopened successfully and again closed excel and again it reopened successfully.

Perhaps you are working on an .xls file as opposed to an .xlt?

HTH

  pc moron 23:56 03 Nov 03

I've definately been working on an .xlt file.

I've created and tried some simple templates and they all report the above error.

I'm doing something wrong, but exactly what I don't know.

I'll tick this as resolved now and search for a tutorial on Excel 2002 and templates/macros!

I have managed to duplicate your fault and hopefully clear it (cross fingers).

I suspect that the buttons on your worksheet have been obtained from the Control Toolbox, certainly that is the only way that I can get your fault to occur.

If however you use the Forms toolbar to give you your sheet button(s) then the macro(s) that you produce for the button(s) is assigned to the appropriate button and there is no fault.


HTH

This thread is now locked and can not be replied to.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

Apple Watch Series 2 review | Apple Watch 2 review: New Apple Watch is faster, brighter,…