Excel File Formats Supported

As you probably know, Excel allows you to save a workbook in a format for earlier versions. In addition, you can save a workbook in a dual-version format that combines two file formats in a single file. These dual-version formats result in a larger file, but unfortunately, these dual-version formats sometimes introduce problems of their own.

If your application must work with earlier versions of Excel, you need to make sure that your file is saved in the appropriate file format. The various Excel file formats that can be saved by Excel 2002 are

Microsoft Excel Workbook (*.xls): The standard Excel 2003 file format. Can be opened by Excel 97, Excel 2000, Excel 2002, and Excel 2003.

♦ Microsoft Excel 5.0/95 Workbook: A format that can be opened by Excel 5.0 and later versions.

♦ Microsoft Excel 97-2000 & 5.0/95 Workbook: A dual format that can be opened by Excel 5 and later versions.

♦ Microsoft Excel 4.0 Worksheet (*.xls): Can be opened by Excel 4 and later versions. This format saves a single sheet only.

♦ Microsoft Excel 3.0 Worksheet (*.xls): Can be opened by Excel 3 and later versions. This format saves a single sheet only.

♦ Microsoft Excel 2.1 Worksheet (*.xls): Can be opened by Excel 2.1 and later versions. This format saves a single sheet only.

♦ Microsoft Excel 4.0 Workbook (*.xlw): Can be opened by Excel 4.0 and later versions. This format saves multisheet files, but they are not the same format as Excel 5 workbooks.

You can use VBA to access the FileFormat property of the Workbook object to determine the file format for a particular workbook. The instruction that follows, for example, displays a value that represents the file format for the active workbook:

MsgBox ActiveWorkbook.FileFormat

Predefined constants are available for the FileFormat property. For example, the statement that follows displays True if the active workbook is an Excel 5 file:

MsgBox ActiveWorkbook.FileFormat = xlExcel5

Table 26-1 lists the constants and values for various Excel file formats.

Table 26-1 CONSTANTS AND VALUES FOR VARIOUS EXCEL FILE FORMATS

Excel Version

Constant

Value

Excel 2.1

xl

Excel

2

16

Excel 3.0

xl

Excel

3

29

Excel 4.0

xl

Excel

4Workbook

35

Excel 5

xl

Excel

5

39

Excel 95/97

xl

Excel

9795

43

Excel in HTML format

xl

Html

44

Excel add-in

xl

AddIn

18

Excel 97/2000/2002/2003

xl

WorkbookNormal

-4143

Determining Excel's Version Number

The Version property of the Application object returns the version of Excel. The returned value is a string, so you might need to convert it to a value. VBA's Val function is perfect for this. The following function, for example, returns True if the user is running Excel 2003 or later (note: Excel 2003 is version 11):

Function XL11OrLater()

XL11OrLater = Val(Application.Version) >= 11

End Function

0 0

Post a comment