To create a DSN for an existing spreadsheet, select the User DSN tab of the ODBC Administrator form and Add a new DSN after selecting the Microsoft Excel Driver from the listbox. Complete the forms by selecting the appropriate Excel Version and browse to find the Excel Workbook you want to reference. Note: select your new DSN from the list and double click to bring up the Setup Dialog box. Select the Options button and make sure the Read Only box is not checked if you intend writing to this device.

In order to access your Excel spreadsheet as an SQL device, it must contain the equivalent of database tables. Any named regions defined in the workbook are "Tables" as far as ODBC is concerned and the first row is taken as the column names. You can have more than one "Table" on the one worksheet. To define regions in Excel go through the menu items INSERT, NAME, DEFINE and type in a "Table" name after selecting the desired region. In Excel 4.0/5.0 and 97, table names already exists for each worksheet within a workbook. In Excel V5.00 they all have a dollar sign ($) as the last character, and you must be sure to include the dollar sign when accessing the worksheet. For example, if you want to access the entire sheet1 of a workbook, you would refer to the table as sheet1$, eg. SQLExec(hSQL, "select * from "sheet1$""). Note that the sheet name is surrounded by double quotation marks. In Excel V4.00, you do not require a $ or double quotation marks. eg SQLEXEC(hSQL, "select * from sheet1"). In Excel97 it appears that the correct syntax is [sheet1$]. Note that you are probably better off defining your own region (ie. table) as the default worksheet tables will contain 256 fields, which is far from optimal for most situations.

1. Using SQL Functions

You are now ready to read and write data to your Excel spreadsheet.

Example 1.

//To read data from a table called myRegion
Int hSQL;
String sData;

hSQL = SQLConnect("DSN=MyExcelWorkbook");
SQLExec(hSQL, "select * from myRegion");
SQLNext(hSQL);
sData = SQLGetField(hSQL, "ColName1");
SQLDisconnect(hSQL);

Note: The file is accessed without needing to run Excel.

Example 2.

//To write data to the spreadsheet :
INT
FUNCTION
LogToSQL()

INT Test;

hSQL = SQLConnect("DSN=Excel_test");
Test = SQLExec(hSQL, "INSERT INTO [Sheet1$] (time1, date1, value1, value2, value3, method) VALUES ('" + Time(1) + "','" + date(2) + "','" + IntToStr(tag1) + "','" + IntToStr(tag2) + "','" + IntToStr(tag3) + "','SQL')" );

RETURN 0;

END

2. Using Dev Functions

You will need to configure a device record that references the DSN and table. For example:

Name: Excel1

The Format field contains all (or a subset) of the database fields defined in your spreadsheet.

The Header field contains the DSN name, eg. DSN=Excel_Test

The File Name references the table name, eg. myregion or [sheet1$] (Note, I have not got the latter syntax working with Excel97).

Type: SQL_DEV

Example

//To write data to the device :
FUNCTION
LogToDev()

hDev = DevOpen("Excel1");

DevWrite(hDev,Time(1));
DevWrite(hDev,Date(2));
DevWrite(hDev,Tag1);
DevWrite(hDev,Tag2);
DevWrite(hDev,Tag3);
DevWrite(hDev,"Device");

DevClose(hDev);
END

Note that if you are only intending to access your Excel spreadsheet via the Cicode Device functions, then you should consider just defining the Excel device as a dBase device rather than an SQL device. Then you will not need to configure or reference a DSN.