Hello Experts,
Is it possible to access the range in xlsm file using Libname xls?
My code is working:
options validvarname=any;
libname xl XLSX "\\XXX\TousIsin.xlsm";
data isin;
set xl.'VALEURS$B2:50'n;
run;
BUT when I replace B2:50 by range NAME:
data isin;
set xl.'VALEURS$test'n;
run;
My code doesn'y work. 😞
Thank you for your help!
I'll recap what we know so far:
You either have a the necessary ACE or JET engine not available, or a bitness problem. See this SAS Note for possible remedies: https://support.sas.com/kb/63/750.html
Hi,
Seems like you've tested it.
AFAIK you'll need the libname excel
libname xlfile excel "your xlsm file" access=read; proc print data=sashelp.vtable (keep=libname memname memtype); where libname eq 'XLFILE'; run;
- Cheers -
Did LIBNAME XLS (which you previously mentioned) actually work? This looks as if you do not have ACCESS to PC Files licensed or installed, or are running SAS on a non-Windows platform.
Run this:
libname xl XLSX "\\XXX\TousIsin.xlsm";
proc sql;
select memname from dictionary.tables where libname = "XL";
quit;
and look at the dataset names you get in the library.
A little research revealed that the XLSX engine does not support named ranges, so you have (AFAIK) three options:
I'll recap what we know so far:
You either have a the necessary ACE or JET engine not available, or a bitness problem. See this SAS Note for possible remedies: https://support.sas.com/kb/63/750.html
Hi,
it looks like you haven't SAS/ACCESS licensed.
You could try with proc Import but I think it won't work neither since it listed in the SAS/ACCESS Interface to PC Files but I would give it a try.
PROC IMPORT OUT= WORK.want DATAFILE= "C:\fileWithNamedRanges.xlsm" DBMS=EXCEL REPLACE; RANGE="range1"; GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;
If none of the proposals work, maybe you can use some vba code to export a named ranges to a new xlsx workbook and Import the data.
I wasn't aware that what you posted before actually works, so thanks for this 🙂
libname xl XLSX "\\XXX\TousIsin.xlsm"; data isin; set xl.'VALEURS$B2:50'n; run;
- Cheers -
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.