Excel: GET.WORKBOOK Returns Error

ID: Q86196

The information in this article applies to:

Microsoft Excel for Windows, version 4.0
Microsoft Excel for the Macintosh, version 4.0

SUMMARY

The GET.WORKBOOK function will return #N/A if the name of the workbook is not specified, and if the Workbook Contents Window is not the active document when the macro executes this function.

MORE INFORMATION

Page 209 of the "Microsoft Excel Function Reference" states that the optional argument to GET.WORKBOOK, "NAME_TEXT," is the name of an open workbook, and if omitted, is assumed to be the active workbook.

This documentation is incomplete. It does not state that the Workbook Contents Window of the active workbook must be the active window for GET.WORKBOOK to return the correct values. If any other window of the workbook is active, and the second argument is omitted, GET.WORKBOOK will return #N/A.

WORKAROUND

Use the Get.Document() function when trying to return the name of the active document in a workbook. If you use the following syntax on a macro sheet:


   A1:  =Get.Document(1)

the name of the active document in the workbook (including the name of the workbook) will be returned to cell A1 on the macro sheet.

If you would like to just extract the name of the active worksheet in the workbook (without the workbook name) use the following syntax on a macro sheet:


   A1:  count=Search("]",Get.Document(1))
   A2:  =MID(GET.DOCUMENT(1),count+1,LEN(GET.DOCUMENT(1))-count)

where "count" is a variable that contains the position of the "]" character in the string returned by the Get.Document(1) function call. The name of just the active document in the workbook should be returned to cell A2 in the macro sheet.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 4, 209, 460

"Microsoft Excel User's Guide 1," version 4.0, page 69

Additional query words:


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 25, 1999