And the answer is... use the old Excel driver, not the new one that comes with Excel 2007.

In particular, use this driver: Microsoft Excel Driver (*.xls) Version 4.00.6305.00 File ODBCJT32.DLL Date 13/04/2009

Do NOT use this one: Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) Version 12.00.6421.1000 File ACEODBC.DLL Date 06/03/2009

The new DSN and working code follows; the end result is a brand new *.XLS file containing a named sheet created from nothing by a SQL Anywhere 11 CREATE TABLE ... AT statement.

Also note that Excel 2007 has no trouble opening this file, unlike the file created using the newer driver.

Here comes the code...

---------------------------------------------------------------------
-- V11 CREATE TABLE AT with Excel

-- Some suggestions for CREATE AT and CREATE EXISTING AT...
--
-- Use the old Excel driver:
--    Microsoft Excel Driver (*.xls) Version 4.00.6305.00 
--       File ODBCJT32.DLL Date 13/04/2009
--
-- If you use the new Excel 2007 driver...
--    Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) Version 12.00.6421.1000 
--       File ACEODBC.DLL Date 06/03/2009
-- you may get this error when you INSERT to the proxy table:
--    Could not execute statement.
--    Server 'EXCEL': [Microsoft][ODBC Excel Driver] You cannot edit this
--    field because it resides in a linked Excel spreadsheet. The ability to
--    edit data in a linked Excel spreadsheet has been disabled in this Access
--    release.
--    SQLCODE=-660, ODBC 3 State="HY000"
--    Line 50, column 1
--    You can continue executing or stop.
--
-- If the dummy.xls file named in the DSN does not exist,
--    it will be created when the CREATE TABLE ... AT runs.
--
-- If the ttt.xls file named in the AT clause does not exist,
--    it will be created when the CREATE TABLE ... AT runs.
--
-- Character strings may be truncated to 64 bytes on 
--    SELECT from an Excel proxy table.
--    Even if that limit can be bypassed, there may be 
--    another limit of 255 bytes.
--    These limits may be imposed by the ODBC driver, not Excel.
--
-- Do NOT use spaces in column titles on row 1, otherwise you will
--    get an error message like "Too few parameters. Expected 8."
--    when you try to SELECT from the proxy table.
--
-- The magic trailing $ may or may not be required...
--    AT 'EXCEL;C:\\temp\\ttt.xls;;ttt$';
--
-- The ReadOnly setting has no effect. If you get a "read only"
--    error message it is likely caused by some other mistake
--    such as using the .xlsx extension in the AT clause
--    instead of .xls.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\excel_files_xls]
"Driver"="C:\\WINDOWS\\system32\\odbcjt32.dll"
"DBQ"="C:\\temp\\dummy.xls"
"DefaultDir"="C:\\temp"
"DriverId"=dword:00000316
"FIL"="excel 8.0;"
"ReadOnly"=hex:01
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\excel_files_xls\Engines]

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\excel_files_xls\Engines\Excel]
"ImplicitCommitSync"=""
"MaxScanRows"=dword:00000008
"Threads"=dword:00000003
"UserCommitSync"="Yes"
"FirstRowHasNames"=hex:01

BEGIN
   DROP TABLE ttt;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE ttt (
   pkey                INTEGER NOT NULL,
   data                VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( pkey ) );

BEGIN
   DROP TABLE proxy_ttt;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER EXCEL;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER EXCEL 
   CLASS 'ODBC' USING 'dsn=excel_files_xls';

CREATE TABLE proxy_ttt ( 
   pkey   INTEGER,
   data   VARCHAR ( 64 ) )
   AT 'EXCEL;C:\\temp\\ttt.xls;;ttt';

INSERT proxy_ttt VALUES ( 1, 'A' );
INSERT proxy_ttt VALUES ( 2, 'B' );

INSERT ttt SELECT * FROM proxy_ttt;
COMMIT;

BEGIN
SELECT * FROM proxy_ttt;
SELECT * FROM ttt;
END;

pkey,data
1,'A'
2,'B'

pkey,data
1,'A'
2,'B'