Microsoft KB Archive/102336

= Microsoft Knowledge Base =

Excel: Can't Open Template with NEW in Auto-Open Macro
Last reviewed: September 12, 1996

Article ID: Q102336

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a
 * Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

SYMPTOMS
When you use the NEW function in an auto-open macro that runs at startup, if you use a saved template name (that is, worksheet, sheet) the macro will halt and will not open a template.

Also, note that page 285 of the "Function Reference" states that you can use quoted text to open a nondefault template. This information is not correct if you use the NEW function in an auto-open macro. If you use a text string enclosed in quotation marks for the type_num argument in the NEW function in an auto-open macro, the macro will halt and will not open a template.

CAUSE
This behavior occurs because the auto-new macro is loaded before the list of template names is loaded in memory, and therefore, the template is unavailable at the time that the NEW function is run.

WORKAROUND
To work around this problem, use the OPEN function instead of the NEW function to open a worksheet based on the saved template.

MORE INFORMATION
You can use NEW with a type_num argument of 1 through 5 to successfully open the standard (default) templates in Microsoft Excel.

Steps to Reproduce Situation
To duplicate the behavior described above:

  In a new macro sheet, type the following macro: A1: Auto_Open A2: =NEW("sheet") A3: =RETURN  From the Formula menu, choose Define Name. In the Name box, type "Auto_Open" (without the quotation marks). In the Refers To box, type "=$A$1" (without the quotation marks). Under Macro, select the Command option. Save the macro in the XLSTART subdirectory (this should be located in the same directory where you installed Microsoft Excel). Close Microsoft Excel Restart Microsoft Excel

Using the formula =OPEN("C:\EXCEL40\XLSTART\SHEET.XLT") will produce a worksheet from the saved template, SHEET.XLT, which should be located in the EXCEL\XLSTART subdirectory.