Excel 2000

Excel is a spreadsheet application that stores and displays data in a proprietary file format. With the Excel 2000 connector, the integration platform can read and write Excel 2000 data files.

Password Protected Sheets - Microsoft does not expose the password component of the Excel API, therefore the integration platform cannot connect to password-protected Excel worksheets. If you encounter an error when trying to connect to a protected worksheet, open the Excel application, unprotect the worksheet and resave the file. Then reconnect to the worksheet in the integration platform.

Excel 2000 can open and save any version of Excel files from Excel 2.1 to the latest version. If you save the file in the same format you opened it in, the file can still be opened in the version of the program in which it was produced. If you save the file to an earlier version format, features used in the workbook that are unique to Excel 2000 may be lost. However, if you choose to update to the Excel 2000 format, you are limited to opening the file in Excel 97 and 2000 only.

Note: The integration platform does not recognize the Accounting data type. Before attempting to connect to a source file with this data type, change the Accounting data type to Currency (within your Excel application).

In Excel 2000, you can use a hybrid file format that allows you to save a file in two workbook formats: Excel 97/2000 workbook format and Excel 5.0/7.0 workbook format. This dual file format allows you to open and view the file in Excel 5.0 or 7.0. It also allows you to maintain the advanced features in Excel 2000. This hybrid workbook contains two file formats, consequently, the file size is roughly twice as large as the Excel 2000 format. Be aware that Excel 5.0 and 7.0 do not recognize the Excel 2000 portion of the file. When you open and revise the file in Excel 2000, the changes are automatically updated in the Excel 5.0 and 7.0 portion of the file.

Note: If you edit the hybrid file and save the changes in Excel 5.0 or 7.0, the Excel 97/2000 file portion is permanently lost. The file contains only the remaining Excel 5.0 and 7.0 workbook file format data.

Microsoft has added several new features in Excel 2000 that do not appear in Excel 97. The integration platform may not read workbooks that contain one or more of the following features:

The integration platform reads and writes the native Excel file format on Windows. There is no native Excel connector for Linux. To run an integration on Linux, the Excel source file should first be converted to a Linux-compatible format such as ASCII. The following are suggestions for converting Excel files so that your transformation can run in Linux production.

• Deploy the integration engine to act as host on the Windows system where the Excel source data resides. Deploy the engine on Linux. The engine can extract and reformat the Excel source data.

• COM Object - Open a process window and create a batch file as a pre-process step and execute this batch file with the engine on Linux. In Excel, the user can save the spreadsheet data into an ASCII .csv file.

• Visual Basic for Application - Embed a VBA script into the Excel spreadsheet that automatically saves the data to an ASCII .csv file every time the user saves the data in Excel.

• Auto-save Excel data to HTML – From the Microsoft website, download the free Excel Internet Assistant Wizard (HTML.exe file). Use the wizard to save Excel spreadsheets directly to HTML format.

Note: For Versions 95/97/2000: If you select the sheet named <Default> from the table list, the connection automatically reverts to the sheet that was selected the last time the file was saved. If you specify a number and no sheet with that number as a name exists, then that number is used as a (0-based) relative sheet number. For example, if a spreadsheet contains Sheet1, Sheet2, and Sheet3 (in that order), then entering a table/sheet name of 2 selects Sheet3. On the target side, if the number does not reference an existing sheet, then a new sheet is created with that number as its name. For example, using the above spreadsheet, if you entered 5 on the target, you would get a spreadsheet containing sheets with names Sheet1, Sheet2, Sheet3, and 5.