I have searched the archives for using insert into to update spreadsheets 
using RODBC and have come up short. So, first off, is it possible?

I have put together a dummy xls table (c:\foo.xls)for exploring 
possibilities of RODBC.  Ultimately, I am interested in replacing much of 
our previous use of vba macros with R ( I'd prefer elimination, but will 
take what I can get ).  In order to achieve this, I will still have a need 
to update spreadsheets directly through R scripts. 

Simple queries seem to work fantastic!  But, I am missing something when 
it comes to writing.

sqlQuery( myChan,"insert into [my customers$] ( CUST_ID, NAME, SOCIAL ) 
VALUES( 5,'robin',5678 ) " )
[1] "[RODBC] ERROR: Could not SQLExecDirect"      "S1000 -3035 
[Microsoft][ODBC Excel Driver] Operation must use an updateable query."


myChan = odbcConnectExcel("c:\\foo.xls")
myChan
RODB Connection 13
Details:
  case=nochange
  DBQ=c:\foo.xls
  DefaultDir=c:\
  Driver={Microsoft Excel Driver (*.xls)}
  DriverId=790
  MaxBufferSize=2048
  PageTimeout=5

sqlTables(myChan)
  TABLE_CAT TABLE_SCHEM             TABLE_NAME TABLE_TYPE REMARKS
1   c:\\foo        <NA> 'my customers orders$'      TABLE    <NA>
2   c:\\foo        <NA>        'my customers$'      TABLE    <NA>
3   c:\\foo        <NA>         'my products$'      TABLE    <NA>
4   c:\\foo        <NA>          'poor table$'      TABLE    <NA>

sqlQuery(myChan,"select * from [my customers$] ")
  CUST_ID        NAME SOCIAL        DOB
1       1    superman   1234 1940-12-31
2       2      batman   2345 1960-01-01
3       3 wonderwoman   3456 1942-05-15
4       4   spiderman   4567 1982-09-30

sqlQuery(myChan,"select * from [my customers orders$] ")
  ORDER_ID CUST_ID       DATE PRODUCT_ID QUANTITY
1        1       3 1997-08-13          1       12
2        2       3 1998-07-23          7       24
3        3       1 1994-01-08          6       11
4        4       4 2001-11-13          5       32
5        5       2 1997-03-09          7        9

sqlQuery(myChan,"select * from [my products$] ")
  PRODUCT_ID PRODUCT_NAME
1          1         cape
2          2         mask
3          3       tights
4          4        boots
5          5      goggles
6          6       gloves
7          7      aspirin
sqlQuery(myChan,"select * from [poor table$] ")
  a bunch of stuff F2  F3          F4               F5        F6
1               NA NA  NA        <NA>             <NA>        NA
2               NA NA  NA        <NA>             <NA>        NA
3               NA NA  NA data_i_like more data I like        NA
4               NA NA 100        blue              hot  94.16857
5               NA NA 200         red             warm  35.85302
6               NA NA 300       green             cold 232.09150
7               NA NA 400        blue             cold  45.40191

sqlQuery(myChan,"select * from [my customers$] A, [my customers orders$] B 
where A.CUST_ID = B.CUST_ID AND A.SOCIAL > 3000", as.is=TRUE) 
  CUST_ID        NAME SOCIAL                 DOB ORDER_ID CUST_ID  DATE 
PRODUCT_ID QUANTITY
1       3 wonderwoman   3456 1942-05-15 00:00:00        2       3 
1998-07-23 00:00:00          7       24
2       3 wonderwoman   3456 1942-05-15 00:00:00        1       3 
1997-08-13 00:00:00          1       12
3       4   spiderman   4567 1982-09-30 00:00:00        4       4 
2001-11-13 00:00:00          5       32
$

f = sqlQuery( myChan,"insert into [my customers$] ( CUST_ID, NAME, SOCIAL 
) VALUES( 5,'robin',5678 ) " )

[1] "[RODBC] ERROR: Could not SQLExecDirect"               "S1000 -3035 
[Microsoft][ODBC Excel Driver] Operation must use an updateable query."

Additional reference: 
http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx

------------------------------------------------------------
CONFIDENTIALITY NOTICE: This electronic mail transmission (i...{{dropped}}

