XL97: Problems When Macro Opens/Saves Text Files with Dates (171278)
The information in this article applies to:
- Microsoft Excel 97 for Windows
This article was previously published under Q171278 SYMPTOMS
In Microsoft Excel 97, if you run a Visual Basic for Applications macro
that opens or saves a text file that contains dates, the following problems
may occur:
- The month, day, and/or year of the dates may be reversed. For example,
if the text file contains the date 5/7/99, the date may be interpreted
as May 7, 1999 instead of July 5, 1999.
- Some dates may be converted into text strings. This problem may occur
if the day or year of a date is greater than 12.
For example, if the text file contains the date 30/7/99 (July 30, 1999),
it may be converted into the text string 30/7/99.
CAUSE
These problems may occur if you are using regional settings that use a date
order of day-month-year or year-month-day.
RESOLUTION
To correct this problem when opening text files, install Microsoft Excel 97
Service Release 1 (SR-1).
Note that the problem may still occur when you save a text file that
contains dates.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Excel 97. The
file-loading problem was corrected in Microsoft Excel 97 SR-1. The file-
saving problem still occurs.
For additional information about SR-1, please see the following article in
the Microsoft Knowledge Base:
172475 OFF97: How to Obtain and Install MS Office 97 SR-1
MORE INFORMATION
In Microsoft Excel, dates contain three elements: a year, a month, and a
day. The order in which these elements are displayed in a date depends on
the regional settings in use on your computer. These regional settings vary
from country to country. The three main ordering methods used for dates in
Microsoft Excel are listed in the following table.
Order July 5, 1999 represented as
--------------------------------------------
month-day-year 7/5/99
day-month-year 5/7/99
year-month-day 99/7/5
The first order, month-day-year, is used in the following regional settings
under Microsoft Windows 95 and Microsoft Windows NT:
English (United States)
Spanish (Dominican Republic)
Spanish (Panama)
If your computer uses any of these three regional settings, the problems
described in the "Symptoms" section in this article should not occur.
However, if you use any other regional settings, you may encounter the
problems described in the "Symptoms" section in this article when you run a
Visual Basic macro that opens or saves a text file that contains dates.
This is because Microsoft Excel 97 may incorrectly use the month-day-year
order for dates. As a result, dates may be converted to the month-day-year
order. The following table contains an example.
Order Date Converted to this order
----------------------------------------------------------------
Day-month-year 5/7/99 (July 5, 1999) 5/7/99 (May 7, 1999)
Year-month-day 99/7/5 (July 5, 1999) 99/7/5 (text string)
Note that if the converted date is not valid in the month-day-year order,
the date is converted to a text string.
These problems do not occur if you manually open or save a text file that
contains dates. The problem occurs only when you use a macro to open or
save such a text file.
This problem does not occur in Microsoft Excel 97 SR-1. This new release of
Microsoft Excel 97 works correctly with all regional settings when you use
a macro to open or save text files that contain dates.
Modification Type: | Major | Last Reviewed: | 10/22/2000 |
---|
Keywords: | kbbug kbProgramming KB171278 |
---|
|