Microsoft KB Archive/105874

= Microsoft Knowledge Base =

Excel: Setting Default Text File Options
Last reviewed: September 12, 1996

Article ID: Q105874

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
 * Microsoft Excel for OS/2, version 3.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0

SUMMARY
In Microsoft Excel, the default settings in the Text Options dialog box (from the File menu, choose Open, and choose the Text button), are: Tab, under Column Delimiter, and Windows (ANSI), under File Origin. Changes to these default settings are not saved with either worksheets or templates, and the defaults will be restored each time Microsoft Excel is restarted.

The only way to change these default settings when Microsoft Excel is started is to create an Auto_Open macro in the \XLSTART directory.

MORE INFORMATION
The following macro code illustrates three methods to change the default text file options when you start Microsoft Excel.

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of that code to suit specific customer requirements.

Example 1: Change Text Options Without Opening Default Sheet1
A1: Auto_Open A2: =ERROR(FALSE) A3: =OPEN(,,,2,,,,3) A4: =RETURN

Explanation of above macro:

A1: Name the macro Auto_Open by selecting cell A1 and choosing Define Name from the Formula menu.

A2: Turn off error trapping if no file name is to be provided in the OPEN statement.

A3: Use the OPEN command to set the delimiter to comma (fourth argument, 2) and the file origin to MS-DOS or OS/2 (eighth argument, 3).

A4: End the macro.

Example 2: Change Text Options and Open Default Sheet1
A1: Auto_Open A2: =ERROR(FALSE) A3: =OPEN(,,,2,,,,3) A4: =NEW(1) A5: =RETURN

Explanation of macro above:

A1: Name the macro Auto_Open by selecting cell A1 and choosing Define Name from the Formula menu.

A2: Turn off error trapping if no file name is to be provided in the OPEN statement.

A3: Use the OPEN command to set the delimiter to comma (fourth argument, 2) and the file origin to DOS or OS/2 (eighth argument, 3).

A4: Open default Sheet1.

A5: End the macro.

Example 3: Change Text Options and Open Custom Sheet1 Template
For Microsoft Excel for Windows:

A1: Auto_Open A2: =OPEN(GET.WORKSPACE(23)&"\SHEET.XLT",,,2,,,,3) A3: =RETURN

Explanation of above macro:

A1: Name the macro Auto_Open by selecting cell A1 and choosing Define Name from the Formula menu.

A2: Use the OPEN command to open SHEET.XLT template (first argument), the location of which is determined by GET.WORKSPACE(23), set the delimiter to comma (fourth argument, 2) and the file origin to DOS or OS/2 (eighth argument, 3).

A3: End the macro

For Microsoft Excel for the Macintosh:

A1: Auto_Open A2: =OPEN(GET.WORKSPACE(23)&":Worksheet",,,2,,,,3) A3: =RETURN

Explanation of above macro:

A1: Name the macro Auto_Open by selecting cell A1 and choosing Define Name from the Formula menu.

A2: Use the OPEN command to open WORKSHEET template (first argument), the location of which is determined by GET.WORKSPACE(23), set the delimiter to comma (fourth argument, 2) and the file origin to DOS or OS/2 (eighth argument, 3).

A3: End the macro