11-28-2017 10:21 AM
In SAS 9.4, I’m trying to use LIBNAME XLSX to read and write to Excel files. I tried to update a named range using SAS 9.4, but it doesn’t work. One of the blog (link provided below) says that “the XLSX engine does not support Excel named ranges”
In SAS 9.3, I was able to update named range cells using the below code. I used “excel” engine to create a libref “AFIL” using LIBNAME statement. “Table_NamedRange” is a named range in the excel spreadsheet located in - "V:\Test\ABC.xlsx”. I was able to update the named range cells in the excel sheet with the data from the SAS table “work.AFILData” (code provided below).
Below is the code I used in SAS 9.3, is there an equivalent code for 9.4?
libname AFIL excel "V:\Test\ABC.xlsx”;
data AFIL.Table_NamedRange ;
How can I update a named Range cell in excel using SAS 9.4?
11-28-2017 10:28 AM
It will help others to help you if you include the exact version of SAS, including maintenance release.
- Latest version as of Nov2017 is 9.4M5 .
Also operating system version and the release/version of EXCEL you are using.
11-28-2017 10:40 AM
This is the error I'm getting when executing the libname statement
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
11-28-2017 11:26 AM
Usually that Class not registered error message is an indication that your SAS and OFFICE installations are using different numbers of bits (32 vs 64). If you cannot update your OFFICE installation to match your SAS installation then you will need to use the SAS PC Files Server to make the connection between SAS and EXCEL.