Excel Macro Functions (GET.DOCUMENT)
Hi All,
Syntax
GET.DOCUMENT(type_num, name_text)
Type_num is a number that specifies what type of information you want. The following lists show the possible values of type_num and the corresponding results.
Type_num Returns
1 If there is more than one sheet in the workbook, returns the name of the worksheet, as text, in the form "[book1]sheet1". Otherwise, returns only the name of the workbook. The workbook name does not include the drive, directory or folder, or window number. It is usually best to use GET.DOCUMENT(76) and GET.DOCUMENT(88) to return the name of the active worksheet and the active workbook.
2 Path of the directory or folder containing name_text, as text. If the workbook name_text hasn't been saved yet, returns the #N/A error value.
3 Number indicating the type of sheet. If name_text is a sheet, then the return value is one of the following numbers. If name_text is a book, then the return value is always 5. If name_text is omitted, then the sheet type is returned. If the book has one sheet that is named the same as the book, then the sheet type is returned.
1 = Worksheet
2 = Chart
3 = Macro sheet
4 = Info window if active
5 = Reserved
6 = Module
7 = Dialog
4 If changes have been made to the sheet since it was last saved, returns TRUE; otherwise, returns FALSE.
5 If the sheet is read-only, returns TRUE; otherwise, returns FALSE.
6 If the sheet is password protected, returns TRUE; otherwise, returns FALSE.
7 If cells in a sheet, the contents of a sheet, or the series in a chart are protected, returns TRUE; otherwise, returns FALSE.
8 If the workbook windows are protected, returns TRUE; otherwise, returns FALSE.
The next four values of type_num apply only to charts.
Type_num Returns
9 Number indicating the type of the main chart:
1 = Area
2 = Bar
3 = Column
4 = Line
5 = Pie
6 = XY (scatter)
7 = 3-D area
8 = 3-D column
9 = 3-D line
10 = 3-D pie
11 = Radar
12 = 3-D bar
13 = 3-D surface
14 = Donut
10 Number indicating the type of the overlay chart. Same as 1, 2, 3, 4, 5, 6, 11, and 14 for main chart above. If there is no overlay chart, returns the #N/A error value.
11 Number of series in the main chart.
12 Number of series in the overlay chart.
The next values of type_num apply to worksheets and macro sheets and to charts when appropriate.
Type_num Returns
9 Number of the first used row. If the document is empty, returns 0.
10 Number of the last used row. If the document is empty, returns 0.
11 Number of the first used column. If the document is empty, returns 0.
12 Number of the last used column. If the document is empty, returns 0.
13 Number of windows.
14 Number indicating calculation mode:
1 = Automatic
2 = Automatic except tables
3 = Manual
15 If the Iteration check box is selected in the Calculation tab of the Options dialog box, returns TRUE; otherwise, returns FALSE.
16 Maximum number of iterations.
17 Maximum change between iterations.
18 If the Update Remote References check box is selected in the Calculation tab of the Options dialog box, returns TRUE; otherwise, returns FALSE.
19 If the Precision As Displayed check box is selected in the Calculation tab of the Options dialog box, returns TRUE; otherwise, returns FALSE.
20 If the 1904 Date System check box is selected in the Calculation tab of the Options dialog box, returns TRUE; otherwise, returns FALSE.
Type_num values of 21 through 29 correspond to the four default fonts in previous versions of Microsoft Excel. These values are provided only for macro compatibility.
The next values of type_num apply to worksheets and macro sheets, and to charts if indicated.
Type_num Returns
30 Horizontal array of consolidation references for the current sheet, in the form of text. If the list is empty, returns the #N/A error value.
31 Number from 1 to 11, indicating the function used in the current consolidation. The function that corresponds to each number is listed under the CONSOLIDATE function. The default function is SUM.
32 Three-item horizontal array indicating the status of the check boxes in the Data Consolidate dialog box. An item is TRUE if the check box is selected or FALSE if the check box is cleared. The first item indicates the Top Row check box, the second the Left Column check box, and the third the Create Links To Source Data check box.
33 If the Recalculate Before Saving check box is selected in the Calculation tab of the Options dialog box, returns TRUE; otherwise, returns FALSE.
34 If the workbook is read-only recommended, returns TRUE; otherwise, returns FALSE.
35 If the workbook is write-reserved, returns TRUE; otherwise, returns FALSE.
36 If the document has a write-reservation password and it is opened with read/write permission, returns the name of the user who originally saved the file with the write-reservation password. If the file is opened as read-only, or if a password has not been added to the document, returns the name of the current user.
37 Number corresponding to the file type of the document as displayed in the Save As dialog box. See the SAVE.AS function for a list of all the file types that Microsoft Excel recognizes.
38 If the Summary Rows Below Detail check box is selected in the Outline dialog box, returns TRUE; otherwise, returns FALSE.
39 If the Summary Columns To Right Of Detail check box is selected in the Outline dialog box, returns TRUE; otherwise, returns FALSE.
40 If the Create Backup File check box is selected in the Save As dialog box, returns TRUE; otherwise, returns FALSE.
41 Number from 1 to 3 indicating whether objects are displayed:
1 = All objects are displayed
2 = Placeholders for pictures and charts
3 = All objects are hidden
42 Horizontal array of all objects in the sheet. If there are no objects, returns the #N/A error value.
43 If the Save External Link Values check box is selected in the Calculation tab of the Options dialog box, returns TRUE; otherwise, returns FALSE.
44 If objects in a document are protected, returns TRUE; otherwise, returns FALSE.
45 A number from 0 to 3 indicating how windows are synchronized:
0 = Not synchronized
1 = Synchronized horizontally
2 = Synchronized vertically
3 = Synchronized horizontally and vertically
46 A seven-item horizontal array of print settings that can be set by the LINE.PRINT macro function:
- Setup text
- Left margin
- Right margin
- Top margin
- Bottom margin
- Page length
- A logical value indicating whether output will be formatted (TRUE) or unformatted (FALSE) when printed
47 If the Transition Expression Evaluation check box is selected in the Transition tab of the Options dialog box, returns TRUE; otherwise, returns FALSE.
48 The standard column width setting.
The next values of type_num correspond to printing and page settings.
to be continued....
