EXTERNSHEET: External Reference (17h)

The EXTERNSHEET record specifies externally referenced workbooks. In BIFF7 and earlier, multiple EXTERNSHEET records form a table in the file. The cxals field of the EXTERNCOUNT record specifies the number of EXTERNSHEET records. You should not change the order of EXTERNSHEET records.

In BIFF8, the SUPBOOK record stores the encoded pathname and file name. There is one SUPBOOK record for each externally referenced workbook. The EXTERNSHEET record contains a table (rgXTI) that points to the SUPBOOK records. Several ptgs in a BIFF8 formula contain an ixti field; this is the 0-based index to the rgXTI table in the EXTERNSHEET record.

An externally referenced workbook is called a source workbook. The workbook that refers to it is called a dependent workbook.

Record Data BIFF8

Offset

Name

Size

Contents

4

cXTI

2

Number of XTI structures that follow

6

rgXTI

var

Array of XTI structures


Each 6-byte XTI structure contains the following data.

Offset

Name

Size

Contents

0

iSUPBOOK

2

Index (0-based) to table of SUPBOOK redcords

2

itabFirst

2

Index (0-based) to first sheet tab in the reference

4

itabLast

2

Index (0-based) to last sheet tab in the reference


Record Data BIFF7 and earlier

Offset

Name

Size

Contents

4

cch

1

Length of the file name

5

rgch

var

File name


The cch field contains the length of the source workbook file name. The rgch field contains the source workbook file name.

File name Encoding

Whenever possible, file names are encoded to make BIFF files transportable across file systems. Encoded file names are identified by the first character of the rgch field. The first character of the rgch field may be any one of the values listed in the following table.

Name

Value

Meaning

chEmpty

00

Reference to an empty workbook name (see text)

chEncode

01

File name has been encoded (see the following table)

chSelf

02

Self-referential external reference (see text)


chEmpty indicates that the file name is an external reference to an empty workbook name, as in the formula =Sheet1!$A$1.

chSelf indicates that the file name is an external reference in which the dependent and source workbooks are the same. An example of this is the workbook SALES.XLS, which contains the formula =SALES.XLS!$A$1.

A chDDE key (03h) can occur in the rgch field; it is not necessarily the first character in the field, as are chEmpty, chEncode, and chSelf. This key indicates that the external reference is a DDE or OLE link. In a DDE link, the chDDE key replaces the | (pipe) character that separates the DDE application and topic. In an OLE link, chDDE separates the classname and file name.

A chEncode at the beginning of rgch indicates that the file name of the source workbook has been encoded to a less system-dependent file name. The special keys listed in the following table are recognized in the rgch field.

Name

Value

PC file systems

chVolume

01

Represents an MS-DOS drive letter. It is followed
by the drive letter. For example, the formula ='D:\SALES.XLS'!A1 generates the chVolume key when the dependent workbook is not on the D
drive. UNC file names, such as
\\server\share\myfile.xls,
generate an @ character after the chVolume key; this replaces the initial double backslash (\\).

chSameVolume

02

Indicates that the source workbook is on the same drive as the dependent workbook (the drive letter is omitted). For example,
the formula ='\SALES.XLS'!A1 generates the chSameVolume key when the dependent workbook is not in the root directory.

ChDownDir

03

Indicates that the source workbook is in a subdirectory of the current directory. For example,
the formula ='XL\SALES.XLS'!A1 generates the chDownDir key. The subdirectory name precedes the chDownDir key, and the file name follows it.

chUpDir

04

Indicates that the source workbook is in the parent directory of the current directory. For example,
the formula ='..\SALES.XLS'!A1 generates the chUpDir key.

chLongVolume

05

(Not used)

chStartupDir

06

Indicates that the source workbook is in the startup directory (the Xlstart subdirectory of the directory that contains Excel.exe).

chAltStartupDir

07

Indicates that the source workbook is in the alternate startup directory.

chLibDir

08

Indicates that the source workbook is in the Library directory.