Using Microsoft Excel

IWS's database interface lets you store information in and retrieve information from Microsoft Excel spreadsheet (XLS or XLSX) files.

You do not need to have Excel installed on the same computer as IWS; IWS can directly read from and write to an existing XLS/XLSX file. However, you do need to use Excel to create the initial file — either a blank spreadsheet for new data or a populated spreadsheet for reference data. Once you've created the file, you can move it to any location and establish a connection to it there.

Note: This procedure was last tested using Microsoft Excel 2007 (12.0.6331.5000).

To establish a connection between your IWS project and your XLS/XLSX file:

In the Database Configuration property sheet, click the Browse button (…).
The Data Link Properties property sheet is displayed:
Figure 1. Selecting the OLE DB Provider
Select the appropriate OLE DB Provider for your database:
- For a Microsoft Excel 2003 spreadsheet file, select Microsoft Jet 4.0.
- For a Microsoft Excel 2007 spreadhseet file, select Microsoft Office 12.0 Access Database Engine.
Click Next.
The Connection tab of the property sheet is displayed:

Figure 2. Specifying the location of the file
In the Data Source text box, type the complete file path for your XLS/XLSX file.
Although the file can be located anywhere on your computer or network, it may be useful to keep it in your project folder. For example:
```
C:\Users\username\My Documents\InduSoft Web Studio v7.0 Projects\project_name\Book1.xlsx
```
If you do this, however, then you must update the file path whenever you move the project folder.
Click the All tab.
Select Extended Properties and then click Edit Value.
The Edit Property Value dialog is displayed.
In the Property Value text box, type one of the following values:
- For a Microsoft Excel 2003 spreadsheet file, type Excel 11.0.
- For a Microsoft Excel 2007 spreadhseet file, type Excel 12.0.
Click OK to close the Edit Property Value dialog.

Editing the value of Extended Properties
Click the Connection tab.
Click Test Connection.
If a connection can be successfully established, then an appropriate message is displayed.
Click OK to save your changes and return to the Database Configuration property sheet.

Note: Be sure to select (check) the Disable Primary Keys option in the Database Configuration dialog. If you do not, then IWS will not be able to connect to your Excel spreadsheet.

Important: Desktop office applications such as Microsoft Access and Microsoft Excel cannot efficiently handle large amounts of data. If you try to save all of your project's historical data in an Access database or Excel spreadsheet, then the queries will become slow and you might get unexpected results. Therefore, we recommend that Access or Excel be used only as a Secondary Database, with the Store and Forward option enabled, or to relay data to third-party software.

To handle large amounts of historical data, we recommend that you use either IWS's proprietary format or a dedicated relational database such as Microsoft SQL Server or ORACLE.