Date: Mon, 10 Mar 2003 16:59:47 -0600
Reply-To: Kevin Myers <KevinMyers@AUSTIN.RR.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Kevin Myers <KevinMyers@AUSTIN.RR.COM>
Subject: Updating Values in an Excel Spreadsheet
Content-Type: text/plain; charset="iso-8859-1"
I would like to use a SAS program to update some values in rows of an existing Excel spreadsheet. I wrote the following step for this purpose:
libname logFile ODBC noprompt="dsn=Excel Files;DBQ=&logFile;";
modify logFile.'LOGS$'n logs;
by Source_File_Path Source_File_Name;
if _iorc_=%sysrc(_sok) then replace;
else if _iorc_=%sysrc(_dsenmr) then do; _error_=0; _iorc_=0; end;
Unfortunately, this produces the error message below:
WARNING: Multiple lengths were specified for the BY variable Source_File_Path by input data sets. This may cause unexpected results.
WARNING: Multiple lengths were specified for the BY variable Source_File_Name by input data sets. This may cause unexpected results.
ERROR: Error updating table entry: [Microsoft][ODBC Excel Driver]Optional feature not implemented
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The data set LOGFILE.'LOGS$'n has been updated. There were 0 observations rewritten, 0 observations added and 0 observations deleted.
NOTE: There were 1 rejected updates, 0 rejected adds, and 0 rejected deletes.
NOTE: There were 2 observations read from the data set WORK.LOGS.
NOTE: DATA statement used:
real time 0.01 seconds
cpu time 0.01 seconds
I am guessing that this less than helpful message means that either SAS or ODBC or the Excel driver for ODBC doesn't support updating individual rows of Excel tables. Does anyone know of a way around this problem other than deleting and completely rewriting the original Excel file?