XL5: Macro Error Using GET.DOCUMENT(1) with Text File

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, if you use the GET.DOCUMENT(1) function in a macro to return the name of the active document, for example to parse or process information in the file, you may receive a macro error if the file is in the Text, CSV, SYLK, or PRN file format.

CAUSE

When you open a file in one of these formats, the worksheet name is Sheet1, instead of the name of the document without the extension. This may cause a macro that uses the name returned by the GET.DOCUMENT(1) function to return a macro error.

The GET.DOCUMENT(1) function returns the name of the active sheet as text, for example, "[Book1]Sheet1." However, when the worksheet has the same name as the workbook, (minus the workbook file extension), the GET.DOCUMENT(1) function returns the name of the file, such as BOOK1.XLS. This behavior is equivalent to the behavior of the GET.DOCUMENT(1) function in Microsoft Excel version 4.0.

Note that when you open a Microsoft Excel version 4.0 Worksheet file, a Lotus 1-2-3 file, or a QuattroPro/DOS file, the worksheet has the same name as the workbook file, and therefore the GET.DOCUMENT(1) function returns the name of the file.

WORKAROUND

To avoid receiving a macro error when you use the GET.DOCUMENT(1) function in a macro to return the name of the document, do either of the following:

• Rename the worksheet "Sheet1" to the name of the file, without the file extension. (Note that this must be done each time you open the file because the worksheet is given the name "Sheet1" each time you open the file)

  -or-

• Use the GET.DOCUMENT(88) function to return the name of the document instead of the name of the worksheet.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

REFERENCES

For more information about GET.DOCUMENT, choose the Search button in Microsoft Excel Macro Functions Help and type:

   GET.DOCUMENT function