Microsoft KB Archive/105874

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:24, 21 July 2020 by X010 (talk | contribs) (Text replacement - "&" to "&")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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

REFERENCES

"Function Reference," version 4.0, pages 135, 285, 307 "Function Reference," version 3.0, pages 68, 169, 159


KBCategory: kbusage

KBSubcategory:

Additional reference words: 3.00 4.00 4.00a



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.