Our system is just upgraded with SAS version 9.1.3. We are trying to use libname statement we used to use for SAS version 8.
here is the ex. of the code
libname dsn2 dbexcel5 '.'
filex="&projname._temp_&ndate" ver=Xls7 spread='A,2,10000,A,K,1,1' check=5;
This isnot working anymore. Is it not supported by SAS 9?
If not what is an another option? We are not licensed for SAS/ACCESS for PC FILE FORMAT. We do have Oledb SAS/Access. We tried using Oledb but we would like to have an option of replacing an existing excel File. Is this possible with what we have.
DBEXCEL5 is one of the engine drivers that used to be part of a product called DBMSCOPY by Conceptual Design Software. See this Tech Support note:
If you go to the Conceptual Software site, you will see that they sold the DBMS/Copy software to SAS back in 2002 as part of the formation of the DataFlux subsidiary. A form of DBMS/Copy was still available, according to their site (www.conceptual.com) as dfPower DBMS/Copy.
The SAS Excel libname engine interface does NOT use engine names that start with 'db'. When you use the SAS Libname engine, the version= option accepts values as shown below (without 'db'): VERSION=2002|2000|97|95|5.
I suspect that your site did not upgrade to the DataFlux version of DBMS/Copy. So that explains why the 'db' syntax is not working. And, since you said that you also did not license the SAS/Access for PC File Formats, that would explain why switching to the Libname engine syntax:
libname myxls "c:\temp\wombat.xls";
would not work for you either.
I am not conversant with OleDB methods, so I can't answer your OleDB question. However, even if you did have SAS Access for PC File Formats and the LIBNAME engine, you cannot replace or overwrite an existing Excel file. (You can issue an X command to delete the previous version of the file.)
Perhaps someone else can discuss the OleDB issues involved in what you need/want to do.